Help Me Please... New to Access and Being Tortured by Dlookup!

[IvanMcGurk]

Hello all,

I am new to this forum and to access so please forgive me for asking schoolboy questions.

I am trying to do something that I know is simple... Dlookup a value into a text box on a form based upon info in another textbox.

I have a Table: Order Details
The table has two fields: Order ID & Unit Price

I have a Form: Orders
The form has two text boxes: Order ID & Unit Price

The unit price text box has the command below assigned to it.

=DLookUp("[UnitPrice]","Order Details","OrderID = " & [Forms]![Orders]![UnitPrice])



Why on earth is this not working? I have copied it form the MS help function so it can't be anything major. It just returns a #Type! error.

I have trawled the forums but can only find solutions for complex Dlookup requests and I cannot get my head around them.

Thanks in advance,

Ivan.

[orange]

Ivan,

Welcome to the forum. Please tell us more about your database and tables.
What is the data type of OrderId --is it Text or Numeric?
I don't think your OrderDetails table has enough fields.
I would avoid names that include embedded spaces.

If you are working on a Customer Order OrderDetails and Item database, then you may
want to watch the videos here.

For DLookup and other Access functions try www.techonthenet.com for samples and descriptions.

Good luck.

[IvanMcGurk]

Hi,

Thanks for the reply.

All of the field are Short Text as they only contain numbers like 100.00 or 150.00. Am I finding problems here because of this??

I literally only have a few fields just try and learn how it all works before trying to apply it to something bigger.

None of the names have spaces on the DB, I picked that up pretty quickly

Thanks,

Ivan.

[orange]

Yes the issue you have is with the text datatype.
A Price field should not be text.
You could use Currency datatype for Currency, Price, Cost,Amount
Things like telephone number, account number... that aren't involved in any arithmetic could be TEXT datatype.

When dealing with text variables in expressions, you must use paired quotes (either " or ') to surround that text variable.
So
=DLookUp("[UnitPrice]","Order Details","OrderID = " & [Forms]![Orders]![UnitPrice])

should be
=DLookUp("[UnitPrice]","Order Details","OrderID = '" & [Forms]![Orders]![UnitPrice]) & "'"


If the variable was a date, you would use # to surround the date variable.
eg MyRequiredDate = "#" & [Forms]![FormName]![PurchaseDate] & "#"

You do NOT need to surround a Number datatype with anything.

See Allen Browne's material for more info.

[ItsMe]

Before you use data type Currency, you should understand its behavior with relationship to the decimal point and rounding. I suggest using data type single or data type double in conjunction with data type currency. Sometimes you can get fractions of a penny when applying division and or percentages to data type Currency. Applying a sum to data type Currency may truncate the sum.

[orange]

@ItsMe

Good point. I was focused on text and Price.

[John_G]

Hi -

The table Order details has a space in the name, so you need to enclose it in square brackets ([]):


=DLookUp("[UnitPrice]","[Order Details]","OrderID = " & [Forms]![Orders]![UnitPrice])

Other than that it looks fine. This may be a case of Access giving you a very misleading error message.

HTH

John

[IvanMcGurk]

Hello all,

Thanks for all the replies.

Unfortunately I cannot get any of the suggestions to work

This is driving me insane as I just cannot get it to work at all. I cannot work out what I am doing wrong, but I know it is something simple. I didn't expect it to be this difficult as I am fairly proficient with Excel!

[June7]

Did you change the data type?

If you want to provide db for analysis, follow instructions at bottom of my post.

I really wonder why you are using DLookup anyway. And why looking for price on the OrderDetails table?

[orange]

Further to the previous posts, tell us more about the database. What is it about? Why are you using DLookUp (thanks June7 for asking the key question)?
It seems you are struggling with a syntax issue, but we don't know enough about your application to be specific.
What tables and fields do you have? What relationships?

Here's a link to a previous post dealing with Products and Prices and why you might include Quantity and AgreeToPrice in the OrderDetails table.
We really need more info about your database and business.

[IvanMcGurk]

I have attached it as a Zip file.

Bear in mind that is is as simple as a few columns and rows of number as all I am doing here is trying to get my head around the basics, this is just a bit that has got me stumped!!

Thanks for all your help

[orange]

Your set up is incomplete.
Do you have Customers
who make Orders which include 1 or more Products, the details of which you keep in OrderDetails?

Did you watch these videos.

[June7]

I agree with Orange. What you have makes no sense. Where are the customer, vendor, product tables? Using DLookup as you have attempted in query makes even less sense. All you have to do is pull the fields down to the grid or select field from the field row dropdown.

Even if using DLookup like this made sense, your OrderDetails table name does not have a space yet you have typed a space into the name in the DLookup expression.

What is purpose of the form? Why is it not bound to OrderDetails table? Why do you have DLookup expression on form?

[IvanMcGurk]

I have just been using the simplest of tables etc to try to understand how each function works before building a bigger data base. So this is purely just to try and figure out how to look up a value from a table and show it in a Form.

I am not concerned with how complete the database is, as once I have figured it out I will begin working on a bigger project.

The problem I have is that, even in this simple case, I cannot understand why I cannot get Dlookup to work properly as I have tried to build it around tutorials and info from MS help...

Literally all I want to get to here is a point where if I type the order number into the Form then I get the unit price displayed instantly in the box below.

Thank you all for your patience, I am a total novice and just want to build up my knowledge gradually

[orange]

See these links
http://www.w3schools.com/sql/sql_join_inner.asp
http://allenbrowne.com/casu-07.html

If you're "playing" with a few concepts to understand/learn, just say so and tell readers
a) the issue
b) your current table set up
c) some sample data
d) what you have tried
e) what error you have specifically.

If you are designing a database, start with a simple statement or picture of the 30000 foot view.
Refine your model or statement as you you start to zero in. As long as your design is holistic(big picture)
you can always design the detail later, knowing exactly where it fits.

The key to getting a focused answer to your post is to provide some detail on the problem in simple terms, plain English.

Also, watch the videos that have been suggested.
Good luck.