Problem with codes

[Sandy]

Following are my codes, which restricts filter depending on dates in Date
Lock Table. When i run this. aDate does'nt get passed on to Me.RecordSource =
"SELECT [Qry Edit Appl Trans].* FROM [Qry Edit Appl Trans] WHERE (([Qry Edit
Appl Trans].ItTransDate)>=(aDateLock))" and system asks for parameter??
Pls help me

Private Sub Form_Load()


Dim aNoLock As Variant
Dim aDateLock As Variant

aNoLock = DLookup("[Date Lock]![Edit]", "Date Lock")
aDateLock = DateAdd("m", -(aNoLock), DateSerial(Year(Date), Month(Date), 1))

Me.RecordSource = "SELECT [Qry Edit Appl Trans].* FROM [Qry Edit Appl Trans]
WHERE (([Qry Edit Appl Trans].ItTransDate)>=(aDateLock))"

 

[Rick Brandt]

Following are my codes, which restricts filter depending on dates in
Date Lock Table. When i run this. aDate does'nt get passed on to
Me.RecordSource = "SELECT [Qry Edit Appl Trans].* FROM [Qry Edit Appl
Trans] WHERE (([Qry Edit Appl Trans].ItTransDate)>=(aDateLock))"
and system asks for parameter?? Pls help me

Private Sub Form_Load()


Dim aNoLock As Variant
Dim aDateLock As Variant

aNoLock = DLookup("[Date Lock]![Edit]", "Date Lock")
aDateLock = DateAdd("m", -(aNoLock), DateSerial(Year(Date),
Month(Date), 1))

Me.RecordSource = "SELECT [Qry Edit Appl Trans].* FROM [Qry Edit Appl
Trans] WHERE (([Qry Edit Appl Trans].ItTransDate)>=(aDateLock))"

The query engine is not aware of your variables. You need to delimit the SQL
String so you pass it the values of the variable instead of the name of the
variable.

Me.RecordSource = "SELECT [Qry Edit Appl Trans].* FROM [Qry Edit Appl Trans]
WHERE (([Qry Edit Appl Trans].ItTransDate)>=#" & aDateLock & "#))"

It would be a good idea to dim your variables as dates rather than variants as
well.

 

[Sandy]

Thanks a LOT!!! It solved my problem as i was stuck for 2 odd days!!

Following are my codes, which restricts filter depending on dates in
Date Lock Table. When i run this. aDate does'nt get passed on to
Me.RecordSource = "SELECT [Qry Edit Appl Trans].* FROM [Qry Edit Appl
Trans] WHERE (([Qry Edit Appl Trans].ItTransDate)>=(aDateLock))"
and system asks for parameter?? Pls help me

Private Sub Form_Load()


Dim aNoLock As Variant
Dim aDateLock As Variant

aNoLock = DLookup("[Date Lock]![Edit]", "Date Lock")
aDateLock = DateAdd("m", -(aNoLock), DateSerial(Year(Date),
Month(Date), 1))

Me.RecordSource = "SELECT [Qry Edit Appl Trans].* FROM [Qry Edit Appl
Trans] WHERE (([Qry Edit Appl Trans].ItTransDate)>=(aDateLock))"

The query engine is not aware of your variables. You need to delimit the SQL
String so you pass it the values of the variable instead of the name of the
variable.

Me.RecordSource = "SELECT [Qry Edit Appl Trans].* FROM [Qry Edit Appl Trans]
WHERE (([Qry Edit Appl Trans].ItTransDate)>=#" & aDateLock & "#))"

It would be a good idea to dim your variables as dates rather than variants as
well.