Setting the formatting of dlookup fields

[decimal48]

Having a weird problem. I have a query and one column is populated by a dlookup command. It grabs the price of something (formatted in currency) from a table, and outputs it to my query. I'm making a report based on this query and I just cannot get it to output it as a currency. The values are also left aligned which I recall reading somewhere meant that the data might be coming through as text instead of numbers?
I tried copying and pasting "$#,##0.00;($#,##0.00)" from the format property of one of my currency fields into the dlookup fields but it did not work.

Anyone have any insight?

edit: Actually this may be more of a query issue then. I cant set the format of the dlookup column in design view of the origin query. The dropdown menu is just blank. And also the data is left aligned in those columns as well. I'm guessing if I fix that issue, I fix the report issue.

[Welshgasman]

Format returns a string.
Use the format of the control in your report.

Unless you are using multiple criteria, probably better to join tables for the price.

[June7]

Maybe use Val() function to convert DLookup result back to number.

When you say "field", do you mean textbox?

[decimal48]

How would I package that line? val(dlookup())? Are there any other operators I need to throw in with val?

edit: yes that worked! I packaged the line as I wrote above, and now I can select currency as a format. Thanks!

[Micron]

Others have said that DLookups in a query don't make a whole lot of sense (partly because they're inefficient), and I tend to agree with them. If you can't include the needed table in the query, there might be something wrong with the relationships. If using it anyway, you should make sure the value it returns is unique else it will return the first occurrence and that might be incorrect. A query would reveal this since your record count would be more than you expect. If you intend to use a query with DLookup as a source for an append or update query, you'd also want to ensure the target field will accept null in case the lookup returns null (if it is to be appended/updated), or be sure it never will.