Using a delimeter in a query field to return a part of the field 1

[bravo6]

Is there a way to ask for everything to the right or left of a delimeter in a query? I'm trying to break a number field that has a decimal into two different fields. For everything right of the decimal I can use (right[field],2) because I know that the numbers only go out to two decimal places. For everything left of the decimal I don't know how many numerals there are. So instead of using "right" or "left" is there a way to use the decimal as a delimeter?

Thanks, dave

 

[HarleyQuinn]

Use LEFT() in conjunction with INSTR()

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[bravo6]

Not really, could you elaborate please?
if the field contained "1234.87" and I just wanted "1234" how would I construct the query?

Thanks

 

[PHV]

I know that the numbers only go out to two decimal places
Leftt([field],Len([field])-3)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bravo6]

Thanks, this helped but has an unexpected consequence. the string somehow changes the property of the field which is a number. Numbers like 378.15 return 378 which is correct. numbers with zeros after the decimal like 378.00 returns "3" because the ".00" is being left off and then it's returning just the "3". I tried to change the property of the field to "number" or "fixed" (which I had to type in because the drop down doesn't work when your string is in the query) but that didn't work.

Thanks.

 

[lespaul]

Why not just INT(fieldName)?

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[bravo6]

That doesn't work either because it rounds. 78.56 came out as 79. ugh this is getting frustrating.

 

[SgtJarrow]

Building on PHV's solution...

Left(Format([field], "###0.00"), Len(Format([field], "###0.00")) - 3)

This forces all the numbers to be shown with decimal point and two places, then strips them off....

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[bravo6]

Eureka! Thanks to all for your help and patience