I have the following code in a sub and I keep getting the following error when the code gets to the set myrecordset line
here is the code
Dim myfirstbegin, myfirstend, mylastbegin, mylastend, strs As String, thedate
Dim nomyage, ten, twenty, thirty, forty, fifty, sixty, seventy, eighty, ninety, hun, myage
myfirstbegin = Nz(Me!FirstBeginningDate, #1/1/1990#)
myfirstend = Nz(Me!FirstEndingDate, #12/31/2021#)
mylastbegin = Nz(Me!LastBeginningDate, #1/1/1990#)
mylastend = Nz(Me!LastEndingDate, #12/31/2021#)
thedate = Date
strs = "SELECT Count(tblClient.Number) AS CountOfNumber, tblClient.FirstVisit, tblClient.LastVisit, " _
& "DateDiff('yyyy',[DOB],#" & thedate & "#) AS myAge " _
& "FROM tblClient " _
& "WHERE tblClient.Gender = 'Male' " _
& "GROUP BY tblClient.FirstVisit, tblClient.LastVisit, DateDiff('yyyy',[DOB]," & thedate & ") AS Age " _
& "HAVING tblClient.FirstVisit Between #" & myfirstbegin & "# And # " & myfirstend & "# " _
& "AND tblClient.LastVisit Between #" & mylastbegin & "# And #" & mylastend & "#"
the erroryntax error (missing operator) in query expression 'DateDiff('yyyy',[DOB],#" & thedate & "#) AS myAge'
and it happens her:
Set MyRecordset = MyDb.OpenRecordset(strs, dbOpenDynaset, dbSeeChanges)
I am parsing in the 'thedate' variable because for some reason MDE files do not process certain functions in hardcoded SQL statements - one of these being Date() function. Unless of course you can tell me why MDB's will run Date functions in SQL statements but MDE's won't. Anyway I cannot figure out why this is not working and I am pretty good at parsing string and sql string statments.
Your HAVING clause should become part of the WHERE clause.
SELECT Count(clients.number) As [count_of_number]
, DateDiff('yyyy', client.dob, #1/1/1990#) As [age]
WHERE clients.gender = 'Male'
AND clients.first_visit BETWEEN #1/1/1990# AND #1/1/2008#
AND clients.last_visit BETWEEN #1/1/1990# AND #1/1/2008#
, DateDiff('yyyy', client.dob, #1/1/1990#)
Run the query from the query window, then transalte into VBA.
I was on a very pressed timeline to finish this , so instead of trying to reolve (which I prefer to do) I was forced to omit the code I was using and write all of the criterai data to a temp table and then update the [DOB] field with DAO code.
It worked but it still does not resolve two issues I am curious abouit
1)Why does runtime access not execute certain functions in SQL that the full Access does?
2) WHY did that code not run using the concatenation i had - it seemed very valid
Anyway - I will get back to this in my free time and contqact you guys if I cannot resolve
I can assure you that the runtime version will execute that function (as will an MDE), and the concatenation method was fine. It's also how I do it. If you have a machine not recognizing basic functions, it's usually a reference problem.
Not sure it was your problem, but I'd point out that you didn't surround the date value with # in one of your DateDiff's. As previously noted, a Debug.Print on the string may have highlighted the problem.