Dlookup not returning Query Results

[Dinger_80]

I have a dlook up that I use on a form. It works on all items in the combo box except one. So all other results are working all right. So I believe my code is working alright. I have checked the record to ensure that nothing seems out of place to cause the issue. I have run the query that the Dlookup refers to and it returns results so I know that the value is not Null, which is the error that I am recieving. Is there a reason that a dlookup would return Null as the answer when Null isn't the answer.

 

[plog]

Possibly the combo box's value isn't what it appears. Your combo box can display one value, but the value it passes could be another.

Or maybe that's true of the query. There could be two spaces between words in the query, but the combo box only has 1 space. Your eyes may not be seeing that additional space. That's especially true if the extra spaces occur after text in a query. So maybe what you think you see in the query, isn't what's actually in the query.

 

[GohDiamond]

Gonna need more information, what are the items in the combo box and which one doesn't it work on? Could be a syntax issue if the selected item is a string and all the other selections look like numbers, or something else like a reserved WORD in Access or a variety of other posibilites that may only reveal themselves upon further research.

Goh

 

[Dinger_80]

The combo box has a few columns. The first column is the autonumber. All items in the combo box come from the main table called TestRequestTable. The combo box only allows items that are active and used at that facility. Originally I only had an issue with one record. It seems that more records are having the same issue. Records are added by engineers who are requesting tests. Each test has its own number. The lab supervisor is trying to assign the tests to technicians. When he selected the original offender a Null response was given. The query returned results based on the combo box. The reason for the dlook up is for the email that gets sent out with information bout the test request. As I said the code works on all previous records. So some 30 numbers it works for I now have 5 where it simply returns a Null value when the query results are clearly no Null.

 

[Dinger_80]

Figured out the problem. It turns out one of the few queries I run didn't return any information and was then making it look like none of my DLookups were working correctly. The issue was that only certain records were lacking the needed information. Once I put that information in I had no problems with things.

 

[namliam]

Dlookup is fine, the moment you add the s for DLookups... you are probably over useing even abusing the DLookup function which is likely giving you some performance issues.


Seriously look at your need to use the DLookup and look into (for example) the use of a recordset instead.

 

[Dinger_80]

That maybe be entirely possible I don't know as I have only been doing this for a little over a year now. From what I understand that the recordset returns the information of the record you are looking for based on your criteria. I haven't found as much on gathering information on related tables though. I understand that a query can be the recordset but I can't seem to return all values under one query to use as a recordset. That is why I used the multiple DLookups to get the information. If there is a way to pull all of this information by all means I would surely be thankful to be pointed down a better path as I believe that could help out other parts of my database.

There are a lot of books on how to do things. There are few on how to do them in more advanced methods. This is a trial and error for me. So I appreciate any ideas that can be offered.

 

[namliam]

Well if you can DLookup the information you can query it....
Difference is one query means one time searching your database.
Each seperate DLookup is a seperate search in your database. Particularly if you are DLookup-ing a query that can be DANGEROUS as the query may take "only" 5 seconds...
Each Dlookup will trigger the query seperately, thus 10 DLookups on the query will take a total of 50 seconds, while the below "sample" of using the query directly will only take 5 seconds one time.

Simply make a query in the query designer, change to SQL view and take that sql
Then in code...

dim rs as dao.recordset
set rs = currentdb.openrecordset("Your SQL")
debug.print rs!YourCOlumnName
Debug.print rs!Anothercolumn
etc...
rs.close
set rs = nothing

Or simularly you can make that query in the designer and save the query as "qryNamliamSample"
Then in code open that:

dim rs as dao.recordset
set rs = currentdb.openrecordset("qryNamliamSample")
debug.print rs!YourCOlumnName
Debug.print rs!Anothercolumn
etc...
rs.close
set rs = nothing

The query name can also be a table name.

 

[Dinger_80]

That might explain some of my hangups that I run into when loading a form. I actually use two queries because I am looking for some different unrelated information. I want to make sure that I understand what you are saying. After I design the query I am want go to SQL view and copy that, then put that SQL inbetween the quotes where you have ("Your SQL")

dim rs as dao.recordset
set rs = currentdb.openrecordset("Your SQL")

So in order to use the information that I have would I then in order to use those values? If I am wrong please point me in the right direction.

debug.print rs!YourCOlumnName = DeclaredValue1
Debug.print rs!Anothercolumn = DeclaredValue2

 

[namliam]

the debug.print only write the information into your immediate pane (CTRL+G)
Which I used as an example, if you want to store data out of your recordset into a variable you do it like so:

DeclaredValue1 = rs!YourCOlumnName
DeclaredValue2 = rs!Anothercolumn

You can also use
rs.Fields("YourColumnName")
or
rs.Fields(SomeVariableInYourVBA)
or
rs.Fields(9)
9 being the column number of the desired column
rs!YourColumnName or rs![Your bad column name] are most commonly used though...

If you dont know why [Your bad column name] is a bad column name please refer to:
http://www.access-programmers.co.uk/forums/showthread.php?t=225837&highlight=naming+convention
Which may be a good idea as well if you have never heared of a thing called "Naming Conventions"

 

[Dinger_80]

Thank you for all of your help, it has been very educational to say the least.