URGENT! VBA and queries

[Everton]

Hi,

I have part of my code that looks like this:

oldstkdate = DMin("date", "qrygdsout")
oldstkarea = DLookup("[areaID]", "qrygdsout", "[date] = #" & qrygdsout & "#")

Where qrygdsout is a query with two citeria based on the values text boxes in the current form. Now, when I run the code it finds the value of oldstkdate fine, but the Dlookupfunction keeps retuning a null value. Is this a syntax problem or somthing deeper.

Any advice is appreciated as I have a deadline looming. Thanks,
Paul.

 

[KDg]

Hi Everton,

the problem is in your criteria clause, you need to specify a date rather than pass in the query.

from the help files :-

An optional string expression used to restrict the range of data on which the DLookup function is performed. For example, criteria is often equivalent to the WHERE clause in an SQL expression, without the word WHERE. If criteria is omitted, the DLookup function evaluates expr against the entire domain. Any field that is included in criteria must also be a field in domain; otherwise, the DLookup function returns a Null.

HTH,

Drew

 

[Everton]

Sorry, I made a mistake in my question. The code should read

oldstkdate = DMin("date", "qrygdsout")
oldstkarea = DLookup("[areaID]", "qrygdsout", "[date] = #" & oldstkdate & "#")

But this still doesnt work. Why?

 

[ElsVanMiert]

I think that the format of the date is the problem, try the following:

dim varDate
oldstkdate = DMin("date", "qrygdsout")
vardate="#" & format(oldstkDate,"mmddyyyy") & "#"
oldstkarea = DLookup("[areaID]", "qrygdsout", "[date] = " & varDate )

 

[Everton]

Hi,
Thanks for that advice. I haven't tried it out, because I solved the problem by individualy checking the day month and year using

oldstkarea = DLookup("[areaID]", "qrygdsout", "Day([date]) = " & Day(oldstkdate) & "AND Month([date])= " & Month(oldstkdate) & "AND Year([date])=" & Year(oldstkdate))

Not very elegant but it works, but I don't want to change it 'cos as they say if it ain't broke...

I'll try reformating the date next time!
Thanks,
Paul.