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

How do i query table using date range value

P: 33
Hi, Im attempting to run a query over a remote Db400 table using MS Access'97 where i need to specify the from and to date. Depending on the current date, the required dates will change so I'm using VB to calculate the day number, to then calculate the from and to dates. The function is called by a query, but it fails on a type mismatch as the target field must be defined as a integer. Given this happens, is there an alternative solution, appreciate as much detail as possible as im not very familiar with writing a sql statement.

Expand|Select|Wrap|Line Numbers
  1. Public Function CalcDelDate() As String
  2.  
  3. Dim FromDelDate As Variant
  4. Dim ToDelDate As Variant
  5. Dim Today As Variant
  6. Dim MyDate As String
  7. Dim Interval As String
  8. Dim FromDate As String
  9. Dim ToDate As String
  10.  
  11. 'This procedure calculates the date for the following days deliveries
  12. 'Calculate what the Day Number is from todays date
  13. 'Where today is a Friday, add 3 days to get to Mondays date
  14. 'Where today is a Saturday, add 2 days to get to Mondays date
  15.  
  16. Today = WeekDay(Date)
  17.     If Today = 6 Then
  18.         FromDelDate = DateAdd("d", "1", Date)
  19.         ToDelDate = DateAdd("d", "3", Date)
  20.     ElseIf Today = 7 Then
  21.         FromDelDate = DateAdd("d", "2", Date)
  22.         ToDelDate = DateAdd("d", "2", Date)
  23.     Else
  24.         FromDelDate = DateAdd("d", "1", Date)
  25.         ToDelDate = DateAdd("d", "1", Date)
  26.     End If
  27.  
  28. 'Now Format the calculated date prefixing with a '1' to match as/400 date format
  29.  
  30.     FromDate = Format(FromDelDate, "1yymmdd")
  31.     ToDate = Format(ToDelDate, "1yymmdd")
  32.     CalcDelDate = ">=" & FromDate & " And " & "<=" & ToDate
Nov 21 '11 #1

✓ answered by NeoPa

The WHERE clause, by necessity, is processed before the SELECT clause. IE. It is not pre-worked out. Not in Jet SQL (The SQL engine used by Access) anyway.

Looking again it's not even there in the SELECT clause so I guess you're less experienced even than that. You mean in the VBA code. I'm afraid variables in code modules are not visible to the Jet SQL engine that interprets the string you pass as a SQL command (in line #8). You need to use Date Literals. Literal DateTimes and Their Delimiters (#) should help you with that.

Share this Question
Share on Google+
4 Replies


P: 33
All, I've now added the following code in an attempt to write an SQL query while using the calculated dates. When the following code runs, it prompts me to input the FromDate and ToDate, despite it already being calculated earlier in the function. Any advice appreciated

Expand|Select|Wrap|Line Numbers
  1. StrSQL = "SELECT AULT2F2_OEP40.CONO40, AULT2F2_OEP40.CUSN40, AULT2F2_OEP40.DSEQ40, AULT1F2_T1P1A.EMIL1A, AULT2F2_OEP40.ORDN40, AULT2F2_OEP40.DTDR40" _
  2.     & " INTO [HOSTLocal]" _
  3.     & " FROM (AULT2F2_OEP40 INNER JOIN AULT2F2_OEP40E ON (AULT2F2_OEP40.CONO40 = AULT2F2_OEP40E.CONO40) AND (AULT2F2_OEP40.ORDN40 = AULT2F2_OEP40E.ORDN40)) INNER JOIN AULT1F2_T1P1A ON (AULT2F2_OEP40.CONO40 = AULT1F2_T1P1A.CONO1A) AND (AULT2F2_OEP40.CUSN40 = AULT1F2_T1P1A.TPAC1A) AND (AULT2F2_OEP40.DSEQ40 = AULT1F2_T1P1A.DSEQ1A)" _
  4.     & " WHERE (((AULT2F2_OEP40.CONO40)='SG') AND ((AULT2F2_OEP40.ORDN40) Not Like 'R%') AND ((AULT2F2_OEP40.DTDR40) BETWEEN FromDate AND ToDate) AND ((AULT2F2_OEP40.CUSO40) Not Like 'AUTODROP%') AND ((AULT2F2_OEP40.OSBT40)<>'E' And (AULT2F2_OEP40.OSBT40)<>'A') AND ((AULT2F2_OEP40E.PBTP40)<>'1'))"
  5.  
  6.     DoCmd.SetWarnings False
  7.  
  8.     DoCmd.RunSQL StrSQL
Nov 21 '11 #2

NeoPa
Expert Mod 15k+
P: 31,709
The WHERE clause, by necessity, is processed before the SELECT clause. IE. It is not pre-worked out. Not in Jet SQL (The SQL engine used by Access) anyway.

Looking again it's not even there in the SELECT clause so I guess you're less experienced even than that. You mean in the VBA code. I'm afraid variables in code modules are not visible to the Jet SQL engine that interprets the string you pass as a SQL command (in line #8). You need to use Date Literals. Literal DateTimes and Their Delimiters (#) should help you with that.
Nov 21 '11 #3

P: 33
Thanks ive sorted, left the WHERE clause in the same location but added " & FromDate & " AND " & ToDate & "
Nov 22 '11 #4

NeoPa
Expert Mod 15k+
P: 31,709
That's the first (major) step towards getting it right and understanding, but if you read through the linked article you'll see that's not the full story. They are date literals so need to be formatted to the specific SQL format (otherwise you will have a USA specific version only (and other locales that use m/d/yyyy format of course) that works correctly).
Nov 22 '11 #5

Post your reply

Sign in to post your reply or Sign up for a free account.