show query criteria on my report

[tonyx666]

ok basically i have a query.

there are many jobs stored on different dates

i use the following criteria

Between [start date:] And [end date:]

to display only jobs between the 2 dates i enter.

this works fine.. however, is there any way that i can display the freshly entered criteria on the report..

perhaps putting something in the control source of a textbox maybe??

so at the top of the report it says

Report from 'this date' To 'that date'

the field is called 'jobdate'

any suggestions?

 

[Remou]

You could create a text box in the header with a Control Source:
[tt]="From: " & Min([jobdate]) & " To: " & Max([jobdate])[/tt]

The disadvantage is that it will show the dates available, not the dates chosen. Alternatively, you could use a small form for the dates and set your query and report to reference the form.

 

[tonyx666]

is there no other way to display the dates entered

 

[dhookom]

Add a text box to your report with a control source of:
="Between " & [start date:] & " And " & [end date:]

You should never use parameter prompts like this for your users. Try to change all of these to use controls on forms.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[tonyx666]

that displays #error

why?

 

[JohnLowell]

I created 2 columns in the Select part of the query to use on the report.

Select ....,[start date:] as StartDate, [end date:] as EndDate
From.......

I hope this helps.....

 

[dhookom]

Tonyx666,
If this is what you entered
="Between " & [start date:] & " And " & [end date:]
then you should not see error. This assumes your record source returns at least one record and the name of the text box is not also the name of a field.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ProgramError]

The error usually occures when there is no data for that particular control (ie null).
As dhookom says, It is better to have the user input the information on a form then open the report and reference the dates fields to the form.



Ian Mayor (UK)
Program Error
Programming is 1% coding, 50% error checking and 49% sweat as your application bombs out in front of the client.

 

[tonyx666]

ok let me try and fix that and ill let you lot know what happens.

thank you

 

[tonyx666]

ok

it now displays

Between 3 7 And 8 7

this is actually what i pressed because i wanted to find out the records from 3rd of July to the 8th of July..

is there any way to format the control to display the value 3 7 as 3rd July??

i know that i should use a form for report generating and i shall do so in the future but right now i need a solution to this if there is one.

 

[dhookom]

Are you actually entering [red]3 7[/red] And [red]8 7[/red] as parameters without any / or - to delimit/format your date values?


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[tonyx666]

yes..

its quicker and easier to do that

is there a way to turn
'3 7' into '3rd July' on the control using some kind of format?

 

[dhookom]

I should have added "How are you using [red]3 7[/red]" to filter your report. I guess Access is being very generous and correctly converting the entry to a date.

You would need to find some code to add the "rd" but can use:
="Between " & Format(cDate([start date:]), "mmmm d") & " And " & Format(cDate([end date:]), "mmmm d")

In the time it has taken to respond and reply and ask questions and post, you could have created a many nice forms for entering your parameters.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[tonyx666]

yes yes i know.. thanks though, it now works fine.