date format

[slimjen]

all,


using access 2003. I have a report that I display a month at a time. Now I want to be able to display a range of date that may overlap months. In my report title my code for the month display is:

=Format$([dtePAIDDTE],"mmmm yyyy",0,0)

How would i do this to display overlap months: ex. Instead of October it would display October - November?
Thanks

[June7]

Is dtePAIDDTE a field from table or criteria parameter?

Maybe:
=Format$([dtePAIDDTE],"mmmm yyyy",0,0) & "-" & Format$(DateAdd("m",1,[dtePAIDDTE]),"mmmm yyyy",0,0)

[slimjen]

Thank you for replying. Yes; "dtePAIDDTE" is a field. I have a query that the report is base on in which it ask for "start date" and "enddate" in the dtePAIDDTE field. Your suggestion works; however if the user just pulls 1 month(october) or 4 months(Sept-Dec), it still gives me the 2 date. How can I do this?
Thanks

[June7]

The startdate and enddate are input by parameter prompts in query? I don't use query prompts, can't validate input and unless the input results in a field that is the input (same value would show for each record), can't use the inputs in other places.

Instead, I have user input criteria on a form. Form remains open when report opens. Report refers to form for input.

=Format$([Forms!formname!starttextboxname],"mmmm yyyy",0,0) & "-" & Format$([Forms!formname!endtextboxname],"mmmm yyyy",0,0)

[slimjen]

O Ok. My reports are based on queries from the data in the table not forms. I thought this was easier because I was creating canned reports for the user that are selected from a switchboard. thanks.

[June7]

The report RecordSource would be a table or a query. The input parameters to filter that data would come from a form.

[slimjen]

? I'm sorry; I don't follow.

[June7]

Show the query SQL statement that the report is based on.

[slimjen]

Ok. See below:

SELECT tblLTL_SHIPMENT.idxBOLID, tblLTL_SHIPMENT.txtBOLNUM, tblLTL_INVOICE.txtINVOICENUM, tblLTL_INVOICE.dtePAIDDTE, tblLTL_INVOICE.txtCARRIERID, tblLTL_INVOICE.txtCARRIERNAME, tblLTL_INVOICE.txtCARRIERACCTNUM, tblLTL_SHIPMENT.dteSHIPDTE, tblLTL_SHIPMENT.txtSHIPTYPE, tblLTL_SHIPMENT.numTRANSCHG, tblLTL_SHIPMENT.numFUELSURCHG, tblLTL_SHIPMENT.numACCESCHG, tblLTL_SHIPMENT.numOTHCHG, tblLTL_SHIPMENT.numTOTALCHGS, tblLTL_SHIPMENT.txtSALESORDNUM, tblLTL_SHIPMENT.txtPURCHORDNUM, tblLTL_SHIPMENT.txtCONSIGN, tblLTL_SHIPMENT.txtCONSIGNCITY, tblLTL_SHIPMENT.txtCONSIGNSTATE, tblLTL_SHIPMENT.txtSHIPPER, tblLTL_SHIPMENT.txtCONSIGNZIP, tblLTL_SHIPMENT.txtSHIPPERCITY, tblLTL_SHIPMENT.txtSHIPPERSTATE, tblLTL_SHIPMENT.txtSHIPPERZIP, tblLTL_SHIPMENT.numWEIGHT, tblLTL_SHIPMENT.txtRESPEXPTYPE, tblLTL_SHIPMENT.txtCONSIGNCTRYREG, tblLTL_SHIPMENT.txtSHIPPERCTRYREG, tblLTL_SHIPMENT.txtEXPTYPE, tblLTL_SHIPMENT.txtSERVTYPE, qry_LTL_ACCOUNTING.txtCOSTCENTER, qry_LTL_ACCOUNTING.txtDIVISION, qry_LTL_ACCOUNTING.txtACCTCODE, qry_LTL_ACCOUNTING.numAMOUNT
FROM (tblLTL_INVOICE INNER JOIN tblLTL_SHIPMENT ON tblLTL_INVOICE.txtINVOICENUM = tblLTL_SHIPMENT.txtINVOICENUM) INNER JOIN qry_LTL_ACCOUNTING ON tblLTL_INVOICE.txtINVOICENUM = qry_LTL_ACCOUNTING.txtINVOICENUM
WHERE (((tblLTL_INVOICE.dtePAIDDTE) Between [Enter Start Date] And [Enter End Date]))
ORDER BY tblLTL_SHIPMENT.idxBOLID;

[June7]

So [Enter Start Date] and [Enter End Date] are input parameter prompts that the user must respond to?

This is what I don't do. Inputs to parameter prompts cannot be validated. The query will attempt to run no matter what the user inputs, easily resulting in bad output and frustration. If you are the only user then maybe you are content to deal with your own input errors, you just start over. Also, responses to prompts in WHERE clause cannot be referred to elsewhere.

Better design is to build a form where user inputs values to controls (textbox, combobox, listbox) then the query would refer to the controls as input.
WHERE (((tblLTL_INVOICE.dtePAIDDTE) Between Forms!formname!startcontrolname And Forms!formname!endcontrolname))

Use the WHERE CONDITION argument of DoCmd.OpenReport (or OpenForm) to pass filter criteria to a non-parameterized form/report RecordSource (VBA example, similar can be done in macro):
DoCmd.OpenReport "report name", , , "dtePAIDDTE Between #" & Forms!formname!startcontrolname & "# And #" & Forms!formname!endcontrolname & "#"

With a form you can validate the user input before running report. You can also refer to the form controls in the report textbox where you calculate the start mon/yr and end mon/yr, as long as the form remains open while the report runs.

[slimjen]

Ok. I understand. I'll try and post back tomorrow.
Thank you for your help!

[slimjen]

Ok. Here's an update. I followed your suggestion. The user wanted a change. Instead of a date to identify a field; they want a month field and filter on this for a report. I tested your suggestion before the change and it worked. However; I no longer need to format the field which would already be month. I took off the format and replaced it with this code because the other would not work and I can't get it to work. This is the code:

=[Forms]![frmMONTHPICK]![STARTMONTH] And [Forms]![frmMONTHPICK]![ENDMONTH]

The name of the form is frmMONTHPICK. Instead of short dates; i have a combo box that list the names of the month followed by the year. i.e. January 2011.
I can't get it to show the Month in the report title.
Thanks

[June7]

A combobox with month name and year means a string, not date value, right? A string value means alpha sort, not number or date. Basing a range on month names will not work.

What is showing in the report title?

[slimjen]

it shows the number 10 and 11. I just figured out it is the number of the month i.e. 10 for October and 11 for November. So; it can't be done in the title with selecting the month?

[June7]

Must be a multi-column combobox that displays the Mon/Yr string but hides the month number column. Have to convert the number to month name. There is no specific function for this. Can use Choose(), like:

=Choose([Forms]![frmMONTHPICK]![STARTMONTH], "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")