How do i query table using date range value

[Quizzed]

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.

Public Function CalcDelDate() As String

Dim FromDelDate As Variant
Dim ToDelDate As Variant
Dim Today As Variant
Dim MyDate As String
Dim Interval As String
Dim FromDate As String
Dim ToDate As String
 
'This procedure calculates the date for the following days deliveries
'Calculate what the Day Number is from todays date
'Where today is a Friday, add 3 days to get to Mondays date
'Where today is a Saturday, add 2 days to get to Mondays date

Today = WeekDay(Date)
    If Today = 6 Then
        FromDelDate = DateAdd("d", "1", Date)
        ToDelDate = DateAdd("d", "3", Date)
    ElseIf Today = 7 Then
        FromDelDate = DateAdd("d", "2", Date)
        ToDelDate = DateAdd("d", "2", Date)
    Else
        FromDelDate = DateAdd("d", "1", Date)
        ToDelDate = DateAdd("d", "1", Date)
    End If
    
'Now Format the calculated date prefixing with a '1' to match as/400 date format

    FromDate = Format(FromDelDate, "1yymmdd")
    ToDate = Format(ToDelDate, "1yymmdd")
    CalcDelDate = ">=" & FromDate & " And " & "<=" & ToDate

[Quizzed]

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

StrSQL = "SELECT AULT2F2_OEP40.CONO40, AULT2F2_OEP40.CUSN40, AULT2F2_OEP40.DSEQ40, AULT1F2_T1P1A.EMIL1A, AULT2F2_OEP40.ORDN40, AULT2F2_OEP40.DTDR40" _
    & " INTO [HOSTLocal]" _
    & " 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)" _
    & " 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'))"
    
    DoCmd.SetWarnings False
    
    DoCmd.RunSQL StrSQL

[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.

[Quizzed]

Thanks ive sorted, left the WHERE clause in the same location but added " & FromDate & " AND " & ToDate & "

[NeoPa]

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).