Report linked to form question 1

[BHScripter]

Hi:

I have a report which I run using a form which asks for a budget month code (i.e. MAR03,
FEB03, etc.)

This code is the criteria that is filled on the query behind the report. (good so far)

Now related to that is an invoice date for every budget month, the invoice date is the 17th of that month.

I would like to be able to have the invoice date fill in on that fields criteria on the query based on the budget month code entered on the form. So if someone wants to see MAR03 then the invoice date would automatically fill on the query as 03/17/03.

Any ideas would be most appreciated. Thanks in advance.

Brianna

 

[CosmoKramer]

Brianna,

Add an expression field to your query. Something like this:

InvoiceDate: DateValue(Left([BudgetMonthCode],3) & " 17, " & Right([BudgetMonthCode],2))

Set the Format of that query field to mm/dd/yy and of course change the name of [BudgetMonthCode] to your field name.....

Let me know if this helps....

 

[BHScripter]

The invoice date is already a field and the Budget Month code is text not a date field.

I do not have the option to change this :-(

So, I need the budget month code to populate the criteria field of the existing Invoice Date field. Since the budget month code is text do I need some sort of if statement to convert the MAR, APR type stuff to numbers?

Thanks

 

[CosmoKramer]

You don't have to change anything, this DateValue statement will convert the code to a date.

Set the control source of your InvoiceDate control on your report to the following, changing [BudgetMonthCode] to whatever you named it:

=DateValue(Left([BudgetMonthCode],3) & " 17, " & Right([BudgetMonthCode],2))

 

[BHScripter]

Ah ha, I see now! It is almost working, the problem now (I didn't realize this would be an issue)

I have tried doing this two ways and it's almost there.
The query which includes the Invoice Date is a subquery of the one which asks for budget month code. So, when I reference that field (budget month code) on the subquery as the field to get the value from, it can't because it wants to run the query with the invoice date first.

Second, I tried to do the same thing and link it to the form unbound text box and that didn't work either.

thanks again for your help

 

[CosmoKramer]

I don't think you shouldn't have to worry about the queries. The budget month code is available to the report, right? You have a text box on your report that you are trying to display the invoice date, right?

If all that is true, then you should be able to set the control source of the invoice date text box on your report to what I said above and you should be all set....

Unless of course I'm missing something.......

 

[BHScripter]

I went back to step one and created the expression field in the query and change the format to short date.

when i run the report from the form it asks me for the InvoiceDate parameter - it's like the value isn't being passed from the expression.

Thanks again for all your help.

InvoiceDate: DateValue(Left([MoCode],3) & " 17, " & (Right([MoCode],2)))


The expression above is typed on the field line in a column in the query window.

 

[CosmoKramer]

Wait...Go back to my previous post. If MoCode is available in the control source of the report, set the control source of the report text box that you want to display the invoice date to:

=DateValue(Left([MoCode],3) & " 17, " & (Right([MoCode],2)))

That should be all you need....