Question Regarding Advanced Query Left([membershipid],InStr([membe

[Guest]

I have a database that has "membershipid" numbers like such:
0001-000 <--- Would be member number 1
0002-000 <--- Would be member number 2

I know that by using the InStr command below I have converted this for a
text field to show as a basic number in the query.
Membershipnum: Left([membershipid],InStr([membershipid],"-")-1)

The question is how can I further sort these numbers If I only wanted to
query membershipid #'s 1 through 5000 again keeping in mind that 5000-000 is
in essence the highest number that I will need to query and 0001-000 is the
lowest number that I will need to include in this query.

I have tried different bits of criteria like between >0 and <5000 as well as

"0" and also >"0-000" and <"5000-000" all seem to give inaccurate query

results.

Any ideas on how to get this idea to work within a query?

Thanks,
Chas

 

[John Vinson]

I have a database that has "membershipid" numbers like such:
0001-000 <--- Would be member number 1
0002-000 <--- Would be member number 2

I know that by using the InStr command below I have converted this for a
text field to show as a basic number in the query.
Membershipnum: Left([membershipid],InStr([membershipid],"-")-1)

The question is how can I further sort these numbers If I only wanted to
query membershipid #'s 1 through 5000 again keeping in mind that 5000-000 is
in essence the highest number that I will need to query and 0001-000 is the
lowest number that I will need to include in this query.

I have tried different bits of criteria like between >0 and <5000 as well as

"0" and also >"0-000" and <"5000-000" all seem to give inaccurate query

results.

Any ideas on how to get this idea to work within a query?

Use the Val() function to convert the text string to a number. Since
Val() reads its argument up to the first character that doesn't make
sense as a number (digits, commas, decimal points, E or D for
scientific notation...) you should be able to just use

MembNo: Val([member number])

as a calculated field. It will contain integer values 1, 2, 3 and so
on.

John W. Vinson[MVP]

 

[Guest]

That did it

Thanks

I have a database that has "membershipid" numbers like such:
0001-000 <--- Would be member number 1
0002-000 <--- Would be member number 2

I know that by using the InStr command below I have converted this for a
text field to show as a basic number in the query.
Membershipnum: Left([membershipid],InStr([membershipid],"-")-1)

The question is how can I further sort these numbers If I only wanted to
query membershipid #'s 1 through 5000 again keeping in mind that 5000-000 is
in essence the highest number that I will need to query and 0001-000 is the
lowest number that I will need to include in this query.

I have tried different bits of criteria like between >0 and <5000 as well as

"0" and also >"0-000" and <"5000-000" all seem to give inaccurate query

results.

Any ideas on how to get this idea to work within a query?

Use the Val() function to convert the text string to a number. Since
Val() reads its argument up to the first character that doesn't make
sense as a number (digits, commas, decimal points, E or D for
scientific notation...) you should be able to just use

MembNo: Val([member number])

as a calculated field. It will contain integer values 1, 2, 3 and so
on.

John W. Vinson[MVP]