Date variable 1

[zimmer9]

I am trying to write Dynamic SQL chunks of code in a form class module.

I have a listbox representing YEARS, which is populated with the following values (established by the user, not me).

Listbox Value for YEARS:
-----------------------------------
ALL - value needs to be translated into the current year
1990 - the years 1990 through 2017 as separate values
1991
...

2017
-----------------------------------

I need to take the Listbox value for the YEAR selected by the user via the listbox. I will call the listbox value chosen (year value) as "Variable X". I need to plug the value selected from the listbox into the following SQL statement:

where DateLost <=DateSerial(X -[tblStatesAll].StocksFS,6,30)

Thus, if the User picks ALL, variable X takes on the value 2005. If the user picks 2017, variable X takes on the value 2017.

Would you have any suggestion as to how I rewrite the SQL where clause to incorporate the value for the year selected by the user bearing in mind that ALL represents the current year while the other possible values are just the years
1990 though 2017 ?

 

[PHV]

WHERE DateLost<=DateSerial(IIf([Forms]![MainForm]![YearListBox]='ALL',Year(Date),[Forms]![MainForm]![YearListBox])-tblStatesAll.StocksFS,6,30)


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

 

[zimmer9]

I tried to execute the following Dynamic SQL statements and I get an Error:
Runtime Error '-2147217904(80040e10)':
No value given for one or more required parameters.

I was wondering what caused the error. My code is as follows (the listbox named lstStates returns a value of 'ALL':

Private Sub Command50_Enter()
Dim strSQLPrefix As String
Dim strSQLSpring As String
Dim strSQLFall As String
Dim strSQLFallCy As String
Dim strSQLProdAll As String
Dim strTest As String
Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = cnn1

strSQLPrefix = "SELECT * FROM tblCust As C, tlbProducts As P, tblStatesAll As S WHERE "
strSQLFallCy = "S.FallCycle = Yes"
strProdAll = " AND C.DateLost <=DateSerial(IIf(Me.lstYears.Value ='ALL',Year(Date),Me.lstYears.Value)-.StocksFS,6,30)"

If Me.Frame60.Value = 1 And (Me.lstStates.Value = "ALL" Or Me.lstStates.Value = "FALL STATES") Then
strSQLPrefix = strSQLPrefix + strSQLFallCy + strProdAll
End If

myRecordSet.Open strSQLPrefix

If I display the SQL string in the Immediate Window, I get the following results:

?strSQLPrefix
SELECT * FROM tblCust As C, tlbProducts As P, tblStatesAll As S WHERE S.FallCycle = Yes AND C.DateLost <=DateSerial(IIf(Me.lstYears.Value ='ALL',Year(Date),Me.lstYears.Value)-S.StocksFS,6,30)

 

[PHV]

Replace this:
strProdAll = " AND C.DateLost <=DateSerial(IIf(Me.lstYears.Value ='ALL',Year(Date),Me.lstYears.Value)-.StocksFS,6,30)"
By this:
strProdAll = " AND C.DateLost<=DateSerial(" & IIf(Me!lstYears.Value ='ALL',Year(Date),Me!lstYears.Value) & "-S.StocksFS,6,30)"

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

 

[zimmer9]

I replaced my sql code with the following:

strProdAll = " AND C.DateLost <=DateSerial(" & IIf(Me!lstYears.Value ='ALL',Year(Date),Me!lstYears.Value) & "-S.StocksFS,6,30)"

this causes a "Compile error: Expected: expression" and the cursor points to the left open quote of the word 'ALL'

 

[PHV]

Oops, my bad, sorry.
strProdAll = " AND C.DateLost<=DateSerial(" & IIf(Me!lstYears.Value ="ALL",Year(Date),Me!lstYears.Value) & "-S.StocksFS,6,30)"

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

 

[zimmer9]

I have created a Dynamic SQL query in concatenated strings.
The first concatenation creates the SELECT ... FROM ... WHERE part of a SQL statement.

I am having trouble with the second string concatenation. It consists of the following statement which gives me a syntax error. I can execute this string without the IIf statement (which I will show after I display the erroneous string) but when I use the immediate if, I must have the syntax incorrectly stated.
----------------------------------------------------------
I get the message: Runtime error '-21427217900(80040e14)'

Syntax error in query expression
'(((C.DateLost)<=DateSerial("& IIf(Forms[frmCustomRpt].[lstYears].Value="ALL",Year(Date),Forms![frmCustomRpt].[lstYears].Value) & ""-.[BondsFS],6,30)
----------------------------------------------------------
The offending string is as follows:

strProdFall = " (((C.DateLost)<=DateSerial("" & IIf(Forms![frmCustomRpt].[lstYears].Value=""ALL"",Year(Date),Forms![frmCustomRpt].[lstYears].Value) & ""-.[BondsFS],6,30)"

this string displays as follow via the Immediate Window:
?strProdFall
(((C.DateLost)<=DateSerial(" & IIf(Forms![frmCustomRpt].[lstYears].Value="ALL",Year(Date),Forms![frmCustomRpt].[lstYears].Value) & "-.[BondsFS],6,30)
------------------------------------------------------------
I originally coded the following string which executes properly before I tried adding the IIf statement.

strProdFall = " (((C.DateLost)<=DateSerial(Year(Now())-.[BondsFS],6,30))"

which displays as follows in the Immediate Window:
?strProdFall
(((C.DateLost)<=DateSerial(Year(Now())-.[BondsFS],6,30))

 

[PHV]

strProdFall = " (((C.DateLost)<=DateSerial(" & IIf(Forms![frmCustomRpt].[lstYears].Value="ALL",Year(Date),Forms![frmCustomRpt].[lstYears].Value) & "-.[BondsFS],6,30)"

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

 

[zimmer9]

Thanks PHV, the following code works great.

How would I interpret the following string variable which is storing part of an entire SQL statement (written in Access VBA) ?

strProdFall = " (((C.DateLost)<=DateSerial(" & IIf(Forms![frmCustomRpt].[lstYears].Value="ALL",Year(Date),Forms![frmCustomRpt].[lstYears].Value) & "-.[BondsFS],6,30)"


C.DateLost is a field within a table
S.BondsFS is a field within a table
Me!lstYears.Value is the value in a listbox

Specifically I'm trying to figure out the meaning of the internal quotes and ampersands ?