year from dropdown

[matrixknow]

Hola,

I should get the year as a parameter from the fields year that I select as a value from a dropdown box.
However on testing first from a simple textbox the query said this is to complex to calculate

year hardcoded works

Between DateSerial(2008-1;3;1) And DateSerial(2008;2+1;0)

trying this not

Between DateSerial([Forms]![Form1]![txtBox1]-1;3;1) And DateSerial([Forms]![Form1]![txtBox1];2+1;0)

How to pass the value ?

 

[Remou]

You could split the parameters up:

[tt]WHERE Year([SomeField])=[Forms]![Form1]![txtBox1]-1)
AND Month([SomeField])=3[/tt]

 

[matrixknow]

I don't see. If this was a SQL Statement I could easily add variable to the string. However, I should pass the parameter (in this case year "2007" or "2008" to the between value.

 

[matrixknow]

strYear = [Forms]![F_DAG]![lstShow].Column(3)

strYear of dateYear should go as a parameter of value to my where between statement

Between DateSerial([Forms]![Form1]![txtBox1]-1;3;1) And DateSerial([Forms]![Form1]![txtBox1];2+1;0)

 

[Remou]

Why use Between? You need a year and month yesno? Hence my suggestion for a change to your SQL string. Where are you using it?

 

[matrixknow]

I use between because he evaluated a date that should from year to year match exactly between the first of march to the last day of februari the year after

 

[Remou]

If a query rather than an SQL string is essential, you can build a query:

strSQL= " <...> Between DateSerial(" & [Forms]![Form1]![txtBox1] & "-1;3;1) And DateSerial(" & [Forms]![Form1]![txtBox1] & ";2+1;0)"

    If DLookup("Name", "MSysObjects", "Name= 'qryQuery'") <> "" Then
        Set qdf = CurrentDb.QueryDefs("qryQuery")
        qdf.SQL = strSQL
    Else
        Set qdf = CurrentDb.CreateQueryDef("qryQuery", strSQL)
    End If

 

[matrixknow]

I figure out if I can work with this. The situation is, is that I have tree queries that should use the value of the dropdown list. These queries are grouped together in a unionquery and they feed a crosstab query.

 

[PHV]

In the 5 queries add the following as the very first line in the SQL view pane:
PARAMETERS [Forms]![Form1]![txtBox1] INTEGER;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886