Dlookup syntax Issue

[SteveE]

Im having problems getting my "simple" Dlookup to work looking for some help please !
I have a table tblEuroHol
with a key fileld made up of Country Code ie IT and a date ie IT04/04/2010

In a form I have a before update event which creates a string CheckD
I am trying to then lookup this field against the tblEuroHol key field as below

I can check datas in the table against my form dates no problem
If DLookup("HolDate", "tblEuroHol", "[HolDate]= ADD") Then

but I need to also check against the CountryCode


Dim CheckD As String
CheckD = Me.CountryCode & Me.ADD

If DLookup("Key", "tblEuroHol", "'[Key]= CheckID '") Then

I am getting error 13 type mismatch and cannot resolve


Any help appriciated
Steve

 

[DCrake]

Firstly when using DLoookups you need to parse the where criteria correctly

DLookup("HolDate", "tblEuroHol", "[HolDate]= ADD")

Should read

DLookup("HolDate", "tblEuroHol", "[HolDate]= '" & "ADD" & "'")

But then again why have a date field that contains text?

Also

DLookup("Key", "tblEuroHol", "'[Key]= CheckID '")

Is meaningless as you are looking up a key that you already know and again it needs parsing

DLookup("Key", "tblEuroHol", "[Key]='" & CheckID & "'")

 

[vbaInet]

Just fyi:

http://www.mvps.org/access/general/gen0018.htm

 

[namliam]

@David
ADD is a control on his form (see the Me.ADD for the CheckID) thus both need to be substituted like your doing the CheckID

If DLookup("HolDate", "tblEuroHol", "[HolDate]= #" & me.add & "#") Then
If DLookup("Key", "tblEuroHol", "'[Key]= '" & CheckID & "''") Then

NOTE 1:
Me.ADD must, that is MUST be in MM/DD/YYYY or YYYY/MM/DD format or your going to run into trouble

NOTE 2:
## is for dates
'' for strings/text
Nothing is needed for numbers, i.e. if CheckID is a number:
If DLookup("Key", "tblEuroHol", "'[Key]= " & CheckID ) Then

Also see the link provided earlier by vbaInet

NOTE 3:
Using multiple DLookups is bad, very bad, VERY BAD... Each is a single query and retrieving data this way is VERY costly in the way of performance.
You would be better of opening a recordset and retrieving the data in a single query, something like:

DIM rs as DAO.Recordset
set rs = Currentdb.Openrecordset("Select ... from ... where .... ") 
If rs!ThisField = "Something" then
.....
If rs!Thatfield = 21 then
.....
if rs!AnotherField > #07/01/2010# then ' (YES in MM/DD/YYYY format)
.....
rs.close
set rs = nothing

 

[SteveE]

Thanks for the speedy replies but I seem to have confused the issue
I am not doing mutipule lookups I was just saying that using the simple first example ie just the ADD date it works but for all Countries.

I was creating a string CheckID by combining 2 fields on my form CountryCode & ADD which in my example will give me IT04/04/2010 then trying to compare this to stored text value in my table tblEuroHol field "KEY"

I will go away and see if I can get it working on your advises

thanks
steve

 

[DCrake]

I think you need to be thinking about naming conventions for your fields/controls. They are very confusing and are also bordering on being Access Reserved words.