Query DLookUp

Question

Good Morning,

I have an issue with a query where I'm trying to lookup a value from a table but can't seem to get the criteria correct. I have attached screenshots with some notes if someone is able to steer me in the right direction.

Any help would be appreciated as I am very new to this.

Thanks

---

DeanGarber

Answer 1

Hi DeanGarber,

Can you also please post the SQL statement for your query? Thanks.

Answer 2

Game_Number in Games table looks like a number.

Game Number in InventoryTable Query looks like TEXT, probably because =Left([Scan],3) returns TEXT since Scan is probably TEXT.

Please display your InventoryForm.

---

If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.

Answer 3

I had considered that but double checked the formats. The ideal situation for me would be to save the values in the table so we can create a few different queries but.....

---

DeanGarber

Answer 4

---

DeanGarber

Answer 5

Hi DeanGarber,

Thanks for posting the SQL statement for your query. You said when you open this query you are prompted for "Game_Number" with underscore (_); however, the SQL statement does not have "Game_Number." I see a "Game Number" without an underscore.

Also, your previous screenshot of the query result shows a "Scan" column. However, I don't see a Scan field in the SQL statement.

Lastly, you said the Game Number is a result of a Left() function, such as Left(Scan,3). Again, I don't see a Left() expression in the SQL statement.

So, are you sure this is the correct SQL statement for the query that is giving you a problem and prompts for a parameter? If so, I can't see how.

You might consider sharing a copy of your db with dummy data to help us see the whole picture better.

Just my 2 cents...

Answer 6

My bad. Wrong Query.

---

DeanGarber

Answer 7

Hi DeanGarber,

That was better. Thanks.

I am not sure if the DLookup() is necessary because you are joining the InventoryTable with the Games table anyway. However, you are using Games.Game_Number = InventoryTable.[Game Number]. Without seeing your table structure, I am thinking this may not be correct. Perhaps you meant to say: InventoryTable.Scan Like Games.Game_Number & "*" in your JOIN. For example, what do you get with this simple query?

SELECT InventoryDate.[Date], 
   InventoryTable.Scan,
   Games.Game_Number,
   Games.Game_Name,
   InventoryTable.[Slot Number],
   InventoryTable.[Ticket Value],
   InventoryTable.[Tickets in Book],
   InventoryTable.[Book Value]
FROM InventoryTable
INNER JOIN Games
ON InventoryTable.Scan Like Games.Game_Number & "*"

Just my 2 cents...

Answer 8

Hello DeanGarber,

Could you share us screenshots of InventoryTable? Including current data and table structure?

Did the InventoryTable have a field "Game Number"? If has, what's the value?

Best Regards,

Terry

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 9

---

DeanGarber

Answer 10

Screenshots of the 2 tables above. The ultimate idea here is to get [Game Name], [Ticket Value], [Tickets In Book], [Game Value] to populate the query based on [Game Number]. Once I get [Game Name] to populate I can figure the other 3 fields out from there.

Each row in the Games table is static which means that [Game Name], [Ticket Value], [Tickets In Book], [Game Value] never deviate from their particular [Game Number]

---

DeanGarber

Answer 11

Hi Dean,

Did you try the query I posted earlier? What was the result?

Answer 12

I did. It asked for the date when I went to open it. See screenshots!

---

DeanGarber

Answer 13

Hi Dean,

Thanks for the update. Let's forget about the Date issue for now. Did you get the matching game name with the correct Game Number or Scan? If so, then you should be able to adapt what I gave you to your situation, so you don't need to use Left() or DLookup().

Regarding the Date issue, it was a typo on my part. For some reason, I called the table InventoryDate rather than InventoryTable. So to fix it, just change InventoryDate.[Date] to InventoryTable.[Date].

Hope it helps...

Answer 14

Excellent. That worked great! THANK YOU! I am trying to add the other fields,  [Ticket Value], [Tickets In Book], [Game Value] to the SQL but am doing something wrong? Any ideas?

---

DeanGarber

Answer 15

Hi,

Looks like you have two FROM clauses. Try taking out the last one.

Hope it helps...

Answer 16

This is the current SQL. My thought was to add ON InventoryTable.Scan Like Games.Ticket_Value & "*" 
after ON InventoryTable.Scan Like Games.Game_Number & "*" BUT that doesn't work either. I guess I'm not sure how to add that?

SELECT InventoryTable.[Date], 
   InventoryTable.Scan,
   Games.Game_Number,
   Games.Game_Name,
   InventoryTable.[Slot Number],
   InventoryTable.[Ticket Value],
   InventoryTable.[Tickets in Book],
   InventoryTable.[Book Value]
FROM InventoryTable
INNER JOIN Games
ON InventoryTable.Scan Like Games.Game_Number & "*"

---

DeanGarber

Answer 17

Hi Dean,

Rather than modify the JOIN clause, have you tried simply adding more Fields? For example:

SELECT InventoryTable.[Date], 
    InventoryTable.Scan,
    Games.Game_Number,
    Games.Game_Name,
    InventoryTable.[Slot Number],
    InventoryTable.[Ticket Value],
    InventoryTable.[Tickets in Book],
    InventoryTable.[Book Value],
    Games.Ticket_Value,
    Games.Tickets_In_Book,
    Games.Game_Value
 FROM InventoryTable
 INNER JOIN Games
 ON InventoryTable.Scan Like Games.Game_Number & "*"

Hope it helps...

Answer 18

Beautiful! THANK YOU!!

Last thing I hope? I noticed that you cant open this Query in normal design mode and I need to add some calculated fields such as the following and since they have values from 2 different tables I'm confused on how to do that?

$ Sold=[POS Number Sold]*[Ticket_Value] 

Tickets Remaining In Book=[Ending Ticket Number]-[Tickets_In_Book]

Curent Ticket Inventory=[Tickets Remaining In Book]*[Ticket_Value]

---

DeanGarber

Answer 19

Hi Dean,

Are you saying two more tables other than the ones already in the query? If so, which table in the current query is related to them?

Answer 20

No its the same two tables 

---

DeanGarber

Answer 21

Good. That should be simple enough. Can you give me an example of the calculations you need to add? Thanks.

Answer 22

$ Sold=[POS Number Sold]*[Ticket_Value] 

Tickets Remaining In Book=[Ending Ticket Number]-[Tickets_In_Book]

Curent Ticket Inventory=[Tickets Remaining In Book]*[Ticket_Value]

---

DeanGarber

Answer 23

Okay, try:

SELECT InventoryTable.[Date], 
    InventoryTable.Scan,
    Games.Game_Number,
    Games.Game_Name,
    InventoryTable.[Slot Number],
    InventoryTable.[Ticket Value],
    InventoryTable.[Tickets in Book],
    InventoryTable.[Book Value],
    Games.Ticket_Value,
    Games.Tickets_In_Book,
    Games.Game_Value,
    InventoryTable.[POS Number Sold]*Games.Ticket_Value AS Sold,
    InventoryTable.[Ending Ticket Number]-Games.Tickets_In_Book AS TicketsRemaining,
    Games.Ticket_Value*(InventoryTable.[Ending Ticket Number]-Games.Tickets_In_Book) AS CurrentInventory
 FROM InventoryTable
 INNER JOIN Games
 ON InventoryTable.Scan Like Games.Game_Number & "*"

Hope it helps...

Answer 24

Ok I got it plus some of the other things I wanted to add.

Thanks a bunch for going out of your way to help.

Very much appreciated!!!!!

---

DeanGarber

Answer 25

Hi,

You're welcome! Glad we could assist. Good luck with your project.