Querie problem

[Schaap]

I got a fill in form with field year1 and year2.
And I got the code below :

If (Not IsNull(Me.Year1)) Or (Not IsNull(Me.Year2)) Then
If IsNull(Me.Year1) Then Year1 = "2000"
If IsNull(Me.Year2) Then Year2 = "2020"
SStr = "#" & (Me.Year1) & "#"
EStr = "#" & (Me.Year2) & "#"
SQLStr = SQLStr & " AND ([Investmentplanning]![Budgetyear] Between " & SStr & " AND " & EStr & ")"
End If

But I get an error :
Syntaxiserror in date. in query-expression :
([Investmentplanning]![Budgetyear] Between "2008" AND "2012"

Does anybody know what I did wrong ?

 

[PHV]

Replace all your stuff by this:
SQLStr = SQLStr & " AND (Investmentplanning.Budgetyear Between " & Nz(Year1, 2000) & " AND " & Nz(Year2, 2020) & ")"


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Elysium]

Where's the first part of SQLStr? All I see is this statement:

SQLStr = SQLStr & " AND ([Investmentplanning]![Budgetyear] Between " & SStr & " AND " & EStr & ")"

Regards,

Randy

 

[Schaap]

The rest of the SOLStr-code :

SQLStr = "[InvestmentPlanning]!IndexNumber > 0"

There are more fields on the fill in form and also included in the SQLStr-code but when I select one of these fields instead of the both fields with Year1 and Year2 it works well. So the problem is probably in the code of my first thread !
I will try the code of PHV now,and will come back on this if it works or not!

 

[Schaap]

I'll tried the SQL-code of PHV but then I get an error that I can't open the resultform !

I open the form with this code :
DoCmd.OpenForm "Selection from database", acNormal, , SQLStr

What can be the problem, and what does Nz do in this code ?

 

[Schaap]

Now I know what Nz function does (nice one).

But there's something going wrong with the code between-and. Because if I change between into like and skip the Year2 part it works !!!

Does someone have a qlue ?

 

[PHV]

What is the data type of Budgetyear ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Schaap]

Data type = Text

 

[PHV]

OK, try this:
SQLStr = SQLStr & " AND (Investmentplanning.Budgetyear Between '" & Nz(Year1, 2000) & "' AND '" & Nz(Year2, 2020) & "')"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Schaap]

It's working thanx a lot !!!