Dlookup problem

Z

zgray

Registered User.
Local time
Today, 14:36
Joined
Mar 8, 2013
Messages
55
I have dlookup that is working correct but for some reason when I put in the criteria of a specific number it doesn't seem to find it in the table. I know its in the table and I even hardcoded the number into it. If I use any other number it works except this one.

Heres my code
Code: 
Me.BranchBuildIn = DLookup("[" & Me.BRANCH & "]", "30YearConv", "[RateNum] = " & Me.RATE & "")

If it makes any difference the number its looking at for rateNum is the last record in the table
 
What is Me.Branch? It suggests that your table is quiet not normalized?

Try..
Code: 
Me.BranchBuildIn = DLookup("[" & Me.BRANCH & "]", "30YearConv", "[RateNum] = " & Me.RATE)
 
If RateNum is a number type then try removing the quotes on the end:
Code: 
Me.BranchBuildIn = DLookup("[" & Me.BRANCH & "]", "30YearConv", "[RateNum] = " & Me.RATE)

If RateNum is a Text type then you will need to add single quotes around it:
Code: 
Me.BranchBuildIn = DLookup("[" & Me.BRANCH & "]", "30YearConv", "[RateNum] = '" & Me.RATE & "'")
 
TJPoorman said:
If RateNum is a number type then try removing the quotes on the end:
Code: 
Me.BranchBuildIn = DLookup("[" & Me.BRANCH & "]", "30YearConv", "[RateNum] = " & Me.RATE)

If RateNum is a Text type then you will need to add single quotes around it:
Code: 
Me.BranchBuildIn = DLookup("[" & Me.BRANCH & "]", "30YearConv", "[RateNum] = '" & Me.RATE & "'")
Click to expand...

RateNum is a number field and I tried that before I posted and still got the same result, and I just tried it again just to make sure. It just doesn't seem to find that number yet I open the table and its right there. Every other one works.
 
Hi

I just came on to post a question and saw this, this is a similar problem as I am having. Very simple bit of code that I have done dozens of times before but not working on this occasion.

If you put your line in vba and step through does BranchBuildIn = Null (thats what I am getting)

going to hold off posting mine and see what comes up here
 
krowe said:
Hi

I just came on to post a question and saw this, this is a similar problem as I am having. Very simple bit of code that I have done dozens of times before but not working on this occasion.

If you put your line in vba and step through does BranchBuildIn = Null (thats what I am getting)

going to hold off posting mine and see what comes up here
Click to expand...

Yea its getting null, I have it vba to begin with. I have also used this many times before... Its weird/frustrating that it doesn't work with just one number when everything else does.
 
Can ya post a Stripped DB?

How to Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)
 
zgray said:
Yea its getting null, I have it vba to begin with. I have also used this many times before... Its weird/frustrating that it doesn't work with just one number when everything else does.
Click to expand...


hope someone is able to help soon...


just to repeat what the guy said above, why you looking up me.branch, should it not just be branch, if that is the name of the field in your 30yearconv table?
 
krowe said:
hope someone is able to help soon...


just to repeat what the guy said above, why you looking up me.branch, should it not just be branch, if that is the name of the field in your 30yearconv table?
Click to expand...

Its pulling the branch name from a field on the form. And yea its a name of a field on the table.
 
sounds like a rounding issue of some sort, try making sure you have the exact same number of decimals, etc on each side of the equation
 
what happens when you hard code the value?

Me.BranchBuildIn = DLookup("[" & Me.BRANCH & "]", "30YearConv", "[RateNum] = %")

Me.BranchBuildIn = DLookup("[" & Me.BRANCH & "]", "30YearConv", "[RateNum] = '%'")

substiting the value that doesnt work where ive put %
 
Hmmm looks like I am practically invisible to the OP. zgray has not responded to any of my suggestions/comments. :eek:
 
I was busy with something else and then I went to lunch. I want to try to avoid uploading this thing if I can.

I already tried to hardcode the number that doesn't work into it and it still didn't work. I'll try it again though.
 
Just to be sure try the following before your dlookup:

Code: 
Debug.Print Me.BRANCH
Debug.Print Me.RATE

Then Hard code the dlookup with what the values are supposed to be:

Code: 
Debug.Print DLookup("[BlahBlahBlah]", "30YearConv", "[RateNum] = BlahBlah")

This should give you an idea of where the code is misfiring
 
The number for RateNum I want it to look up was 0.05125 and it didn't work. I put 5 in and it worked and then I put 0.04875 and it didn't work and then I put 4 in and then it worked. It seems as though its not looking past that second decimal spot? Now im really confused. The numbers are all in the table the dlookup is looking at.
 
Sounds like an issue with what type of number you are using. If you have Integer or Long Integer anywhere, then this will only look at the whole number. If you are using decimal places you need to have the type as Double.
 
TJPoorman said:
Sounds like an issue with what type of number you are using. If you have Integer or Long Integer anywhere, then this will only look at the whole number. If you are using decimal places you need to have the type as Double.
Click to expand...

Sorry I misstyped what I said in my last post. I put 0.05 and 0.04 not 5 and 4. And the 0.05 and 0.04 worked. I checked my tables just to make sure it was Double and they are.
 
zgray said:
Sorry I misstyped what I said in my last post. I put 0.05 and 0.04 not 5 and 4. And the 0.05 and 0.04 worked. I checked my tables just to make sure it was Double and they are.
Click to expand...

Ok now for some reason every other number is working except the on I originally had the problem with. I went into the table and right below the number that wasn't working I typed it in again and it worked. I really don't know why its doing this. Its skipping over just that one and yet saw the other one I hand typed in.
 
is it worth trying to put a trim around the parameters? - may be barking up the wrong tree...
 
You must log in or register to reply here.

Similar threads

D
Solved DLookup Not Changing Value
Replies
14
Views
1,355
DDJKSA
D
N
Solved VBA blocking the saving of foreign key and line numbering in the subform and child table
Replies
7
Views
386
Minty
Minty
AsMok
Solved dlookup multi criteria
Replies
4
Views
870
AsMok
AsMok
V
DLookup on a SQL statement
Replies
5
Views
821
VSolano
V
L
DLookup Problem
Replies
14
Views
1,306
plog
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom