By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,445 Members | 3,172 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,445 IT Pros & Developers. It's quick & easy.

problem calling date function from query

P: n/a
Hello,

I have a question regarding how to format a date in VB so that I can
call it from a query and get results. I'm calling functions in the
query because that was the only way I found I could set up a query
with multiple parameters to return data the way I wanted. (I'm reading
up on a filter recordset command that will probably do this much
better, but I haven't got it figured out yet.) The query calls all the
functions and pulls up data exactly as I want except for the date
field.

I can't get the query to read the date defined in the function at all.
I've simplified the routine so you can see the syntax I've tried. If I
can get it to read something simple like this than I can get it to
evaluate the date span I want it to. There should be 7 records
displayed with the date Jan/02/2004, but these all return empty sets.

ATTEMPT 1
CODE:
Public Function srcDate3() as String
srcDate3 = "#01/02/2004#"
End Function
QUERY:
Field: DateRec
Criteria: srcDate3() [I also tried "like srcDate3()]

ATTEMPT 2
CODE:
Public Function srcDate3() as Date
srcDate3 = cdate(01/02/2004)
End Function
Query:
Field: DateRec
Criteria: srcDate3()
Note: cdate formatted as mm/dd/yyyy

Ideally I would like to call a string "Between mm/dd/yyyy And
mm/dd/yyyy" where the function determines the mm/dd/yyyy of the span,
but I'm not sure I can do that by calling in a function.

Thanks in advance,

Andy
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Hmm... not sure what's going on, but this works....

Query:
SELECT Table1.SomeDate, Table1.RecNo
FROM Table1
WHERE (((Table1.SomeDate) Between Tomorrow(Date()) And NextWeek(Date())));

'Functions...
Option Compare Database
Option Explicit

Public Function Tomorrow(ByVal dtToday As Date) As Date
Tomorrow = dtToday + 1
End Function

Public Function NextWeek(ByVal dtToday As Date) As Date
NextWeek = DateAdd("ww", 1, dtToday)
End Function
Does that help?
Nov 13 '05 #2

P: n/a
Did this the other day... worked a champ.

Dead stupid functions...

Option Compare Database
Option Explicit

Public Function Tomorrow(ByVal dtToday As Date) As Date
Tomorrow = dtToday + 1
End Function

Public Function NextWeek(ByVal dtToday As Date) As Date
NextWeek = DateAdd("ww", 1, dtToday)
End Function

SELECT Table1.SomeDate, Table1.RecNo
FROM Table1
WHERE (((Table1.SomeDate) Between Tomorrow(Date()) And NextWeek(Date())));
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.