Pass unbound form control to query

[coasterman]

Hi

I have some pretty basic code on the form current event which after navigating a number of logical statements generates a date.

that value is then reflected in an unbound control txtLimitationDate on my users form. i.e

Me.txtLimitationDate.Value = LimitationDate



I would however like to use the value LimitationDate in a select query but don't know how to go about this?

Is this even possible in SQL or would I need to create a recordset in VBA? (which I suspect is probably beyond me at this point)

I know storing calculated value is tables is bad practice but I'd be quite prepared to go that route if anyone could point me in that direction.

many thanks

 

[coasterman]

Hi,

Thanks for the info but I'm still not clear I'm afraid.

Something pretty fundamental I'm missing here I'm sure. Forgive me for hashing this out long hand but its the only way I can think to explain it.

I have a 2 bound date fields on a frmMain which in conjunction with the present Date() are passed through various if statements in the forms code to arrive at the vba variable LimitationDate

There is an unbound txtLimitationDate control on the same frmMain i.e Me.txtLimitationDate.Value = LimitationDate

This is where I lose the plot I beleive?

If the value of Limitationdate is only determined on the forms VBA on current event how does the query 'run' (for want of better expression) the VBA to get the values for Limitationdate in each record?

For testing I tried adding a field [todaysDate] to the tblMain table (the record source for frmMain) with default value NOW() to see if the following would work

SELECT *
FROM tblMain
WHERE (((tblMain.todaysDate)<Forms!frmMain!TxtLimitationDate));

I just seem to get every record where [todaysDate] has a value in the table

It may help if I say the final query intended should select all records where the LimitationDate value of each record is less than 60 days from today.

Hope this make some sort of sense?

 

[coasterman]

Apologies for the delay returning on this but been away. It turns out I do need the VBA calculated value stored in the table for other duties so set the record source from the Me.txtLimitationDate control to a field in the table.

Thanks for looking and I certainly picked up a new tool in your reply re referencing the control on the form in an SQL statement.