DIM and Set db once per module

[Guest]

The only thing I have to fix is my query. When the .exe searches the query I
get the following error.
Unidentified function 'NextBday' in expression
the following is the query sql
*******Query Sql********
SELECT FirstName & " " & LastName AS FullName, CVDate(NextBDay([BirthDate]))
AS [Upcoming Birthday]
FROM Contacts
WHERE (((CVDate(NextBDay([BirthDate])))<=Date()+30));
*******End*******

 

[TC]

Well, I'm still confused - and you still haven't answered!

At the risk of being repetetive:

- Is it a *** Visual Basic (VB) *** exe file?

- If so, why don't you do it *** directly in Access *** ?

TC

 

[Tim Ferguson]

SELECT FirstName & " " & LastName AS FullName,
CVDate(NextBDay([BirthDate])) AS [Upcoming Birthday]
FROM Contacts
WHERE (((CVDate(NextBDay([BirthDate])))<=Date()+30));

I am not sure what NextBDay() refers to either: it's not a recognised Jet
function.

I have a suspicion that this is a function that you have defined either
somewhere in your VB.EXE or your .MDB file. In either case, it won't
work, because the db engine cannot see either of them. When you run
Access from the user interface, there is an expression evaluator thingy
that interprets any queries you run and does some magic to make the VBA
stuff work. When you are talking direct to Jet, though, you don't get any
of that kind of help and have to rely on SQL alone.

That said, it's still possible to write NextBirthday() functionality in
plain SQL itself. As long as you are using DAO, the version in the
original script I posted works:

DATESERIAL(
YEAR(DATE()) + IIF(
DATESERIAL(YEAR(DATE()),MONTH(Birthday),DAY(Birthday))>=DATE(),
0,1
),
MONTH(Birthday),
DAY(Birthday)
)


It's certainly possible using the ADO library but the names of the
individual functions will be different.

Hope that helps


Tim F