Dlookup and format

  • Thread starter Thread starter Suzanne
  • Start date Start date
S

Suzanne

I'm fairly new to access - especially VBA and I'm sure I'm missing something
obvious but can't figure it out...

I have a form (Access 2007) that uses a Dlookup to bring in the cost of an
item once the item# is selected my code is as follows:

Private Sub Item__AfterUpdate()
[Item Cost] = DLookup("[Item Price]", "Items",
"[Item#]=[Forms]![WorkOrder]![WO Line].Form![Item#]")
End Sub

The problem is that when it populates the cost it is rounding it to the
nearest dollar. I've tried the decimal places at both "auto" and set at 2
with no difference and I can't find a spot where it would indicate to round
this. Am I missing something from the code or is this a form formatting
thing? Any help would be appreciated.

Thanks!
 
Suzanne,
Check your table design.

The field you are updating with the DLookup needs to be a
Single or Double numeric field type. Those types can handle
non-whole number values.

If you've set it up as a Byte, Integer, or Long Integer... those
numeric types can only store whole numbers... thus the rounding
when updated.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."
 
I should have known that - thanks for the help!

Al Campagna said:
Suzanne,
Check your table design.

The field you are updating with the DLookup needs to be a
Single or Double numeric field type. Those types can handle
non-whole number values.

If you've set it up as a Byte, Integer, or Long Integer... those
numeric types can only store whole numbers... thus the rounding
when updated.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."


Suzanne said:
I'm fairly new to access - especially VBA and I'm sure I'm missing
something
obvious but can't figure it out...

I have a form (Access 2007) that uses a Dlookup to bring in the cost of an
item once the item# is selected my code is as follows:

Private Sub Item__AfterUpdate()
[Item Cost] = DLookup("[Item Price]", "Items",
"[Item#]=[Forms]![WorkOrder]![WO Line].Form![Item#]")
End Sub

The problem is that when it populates the cost it is rounding it to the
nearest dollar. I've tried the decimal places at both "auto" and set at 2
with no difference and I can't find a spot where it would indicate to
round
this. Am I missing something from the code or is this a form formatting
thing? Any help would be appreciated.

Thanks!
Click to expand...
Click to expand...
 
Back
Top