Dynamic Where statement

[psatkar]

Hi,

I have a requirement in which I need to build the "where" statement of a select query as follows:

select * from transactions_details
where format(trans_date, "m") = format(date(), "m")

I need to make this option from format function to be dynamic so that it can either be M (for months) or "yyyy" (for year) or "q" (for quarter)

I have a form having a combo box on it. The query parameter needs to be changed as Month / Quarter/ Year based on the this combo box selection. I can write after update even on the combo box selection. However, I am not able to find out how I can change these values in query parameter.

I tried to defined to 2 text boxes (txtData1 and txtData2) on this form. Based on combo-box selection, I populate text box with values as txtData1 = format(trans_date, "m")' and txtData2 = 'format(date(), "m")' and I modify the query like

select * from transactions_details
where [Forms]![fmrName]![txtData1] = [Forms]![fmrName]![txtData2]

But it does not seem to work.

Can you guide me how I can achieve this?

Thanks in advance

--psatkar

 

[SimonB1978]

Hi,

I'd try something like this:

select * from transactions_details
where format(trans_date, [Forms]![fmrName]![YourComboBox]) = format(date(), [Forms]![fmrName]![YourComboBox])

Simon B.