dlookup criteria problem

[Soulspike]

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

[RuralGuy]

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

[Soulspike]

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

[RuralGuy]

See if this link helps:
http://www.mvps.org/access/general/gen0018.htm

[Stewart Ross]

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

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

-Stewart