By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,064 Members | 1,449 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,064 IT Pros & Developers. It's quick & easy.

dlookup criteria problem

P: 35
Hello all.

Currently I am still useing access97 (sad I know) and I can not seem to get this dlookup function in my vb code to work. I keep getting a type mismatch error when I run the code. I am sure it is my formating of the line but cant seem to get it right. I verified my fields are all the correct data type.

Dim FirstQin As Long
Dim FrmDate As Date
Dim frmShp As String

FrmDate = [Forms]![Main Form]![tempDate]
frmShp = [Forms]![Main Form]![Shop]

FirstQin = DLookup("[1Qin]", "Input", "[Shop] ='" & frmShp & "'" And "[TempDate] =#" & FrmDate & "#")

Any help would be greatly appretiated
Nov 20 '08 #1
Share this Question
Share on Google+
4 Replies


RuralGuy
Expert 100+
P: 375
Too many quotes!
FirstQin = DLookup("[1Qin]", "Input", "[Shop] = ' " & frmShp & " ' And "[TempDate] =#" & FrmDate & "#")
Nov 20 '08 #2

P: 35
first, thank you for taking the time to respond to my question. I am still fairly new to the VB side of things and this forum has been a life saver.

I copied the code into my module and I got a compile error: Expected: list separator or ).

I tried a couple things but couldnt get it to work. Any additional help is appretiated
Nov 20 '08 #3

RuralGuy
Expert 100+
P: 375
See if this link helps:
http://www.mvps.org/access/general/gen0018.htm
Nov 21 '08 #4

Expert Mod 2.5K+
P: 2,545
Hi all. The quotes after the "'And " part are not quite right. There is also an extra space after and before the single quotes for "[Shop] = ' " which I have removed. These extra spaces will themselves prevent the Dlookup where clause from working, as being within the single quotes they become part of the string to be matched.

Corrected version is
Expand|Select|Wrap|Line Numbers
  1. FirstQin = DLookup("[1Qin]", "Input", "[Shop] = '" & frmShp & "' And [TempDate] = #" & FrmDate & "#")
-Stewart
Nov 21 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.