Query problems - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Query problems

Maarten Brusselers

4 years ago

As Kevin Zip recommended I put all my planting data in one table. My Loss by lot no query works but when only the Planting and Seeding records have data and the Growth record does not it does not show a result in the Total loss. As soon as I put a value in the Growth loss I get the final Total Loss. The Value for the Total loss is SeedingLoss + NurseryLoss + GrowthLoss. I would think that when there in no value in GrowthLoss the TotalLoss would show SeedingLoss + NurseryLoss.
Also, I cannot get the Query to find the LotNo value from the form LotNoF. I have done this before with another query without problem but I have been struggling with this for hours and no idea why it does not work.

	Maarten Brusselers OP @Reply 4 years ago

	Maarten Brusselers OP @Reply 4 years ago

	Maarten Brusselers OP @Reply 4 years ago

	Kevin Robertson @Reply 4 years ago
The problem is the Null values. See: NZ Function. Is the first column of the Combo Box an ID? If so, you may have the criteria in the wrong field.

	Maarten Brusselers OP @Reply 4 years ago
Hi Kevin, NZ Worked, thanks. The Combobox has only 1 column, LotNo, although it automatically includes the LotID in the Select string. See screenshots

	Maarten Brusselers OP @Reply 4 years ago

	Maarten Brusselers OP @Reply 4 years ago

	Kevin Robertson @Reply 4 years ago
The Combo Box does actually have 2 columns. If you only want one column you will have to remove the ID manually and also change a couple of properties on the Format Tab. See Screenshot. Note: The Wizard adds the ID field automatically if the Combo Box is based on a table. This doesn't happen if they are based on a Query.

	Kevin Robertson @Reply 4 years ago

	Maarten Brusselers OP @Reply 4 years ago
Hi Kevin, Thanks a Lot. Now it works. I've spend hours trying to find out why it would not work. I guess I'm not far enough in the course yet to know this. Why is it not possible to look up a value from a combobox with the ID in there?

	Maarten Brusselers OP @Reply 4 years ago

	Kevin Robertson @Reply 4 years ago
You would need the ID field in the Query then add your criteria to that. You could also change the Bound Column in the property sheet. There are several ways to handle most things in Access.

	Maarten Brusselers OP @Reply 4 years ago
Hi Kevin, BTW, why did DLookUP("LotNoCombo","LotNoF") not work? It seemed Access would only look for tables and queries when I tried it just now.

	Kevin Robertson @Reply 4 years ago
You can't use DLookup to look up values on a form. Forms don't contain any data.

	Maarten Brusselers OP @Reply 4 years ago
Hi Kevin, that makes sense, thanks.

	Kevin Robertson @Reply 4 years ago
You're welcome.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View