Creating date codes for query to preview report from a form

[idd]

Hi All,

I'm using Access 2000

First i'll mention what I want to achieve and the scenario, then what i have done and the problem. This way if anyone has any alternate ideas it will be easy for them to understand (hopefully)

i'll try and explain as well as I can.


What I want to achieve
I want to be able to click a button on a form which will show a print preview of a a report, where the date criteria of the underlying query is drawn from date selections made by the user on a the form.

The scenario

I have the activeX calendar, an option group which has the options single date and between dates
I also have two text boxes, named TXT_Start and TXT_End.

If the user selects single date it sets a boolean field (TwoDates) in the form code to false. If the user selects " between dates " it sets TwoDates to true.

If Twodates = false then clicking on the calendar will only update the TXT_Start text box, otherwise if twodates = true it will alternate in updating the TXT_Start and TXT_End on each click of the calender.

What I have done to get my form to send these dates to the query which will then bring up the desired result on the report is as follows.


Clicking a button calls the code to preview the report and also creates some code placed in a TXT box called TXT_Code

The query's criteria is set to the text box TXT_Code

The code which is generated is basically

This sub is called 'Make Code'

If TwoDates = False Then
txt_Code = Me.Txt_start
ElseIf TwoDates = True Then
txt_Code = "between #" & Txt_start & "# and #"& _ Txt_End & "#"
End If


the code behind the button is



Dim stDocName As String
stDocName = "RPT_Lbls4StudPacks"

MakeCode mentioned above

DoCmd.OpenReport stDocName, acPreview



for a single date it works fine, for between dates it doesnt seem to work.

Final Thoughts
I think the reason single dates works is because it only has a date as the criteria and the query will place # around the date, whereas for the between it really is text being passed and I think Access will then place it in the criteria of the query with ' (Apostrophes), around it, which then wont work.

I also think I am doing this the long way around, i'm sure someone will come up with an answer which involves just using the where clause in the openreport or something like that, anyway over to you lot.

I hope this makes sense, and hope someone can help.

Idd

 

[jrbarnett]

For more on running reports from selection criteria forms take a look at:
thread181-542991 I answered in quite some detail recently.

John

 

[idd]

John,

In your answer in the thread mentioned above, you answer states to make the report based on the SQL statement at the top of your query(i think it was code).

However I already have a query designed and a report based on it, I just want to use the code to add a where clause or to filter the results.

The method I was using above involved creating code to put in the criteria of the query's QBE grid.

I have changed it slightly and have created a module level variable for the where string called StrWhere, the code is then put in to this variable and used as the where clause in the openreport command

it works for both a single date and for between two dates, with the exception that for single dates it doesnt always work. I cant figure out why, it will return the answer most of the time but for two particular dates 7th April & 9th April 2003 it wont find the data.



-------------------------------------------------------------------------------------
the code I am using is as follows, this is the variable StrWhere is used in the where clause of the OpenReport command.

Any help would be appreciated.

Idd

 

[idd]

Whoops I forgot to put the code in,

Here it is
-------------------------------------------------------------

StrWhere = "Stu_EnqDate "

If TwoDates = False Then
StrWhere = StrWhere & "= #" & Me.Txt_start & "#"
ElseIf TwoDates = True Then
StrWhere = StrWhere & "between #" & Txt_start & "# and #" & Txt_End & "#"
End If

--------------------------------------------------------------

 

[jrbarnett]

Hello

The code you wrote looks valid, I can't see anything wrong with it (assuming the fieldnames are valid). I also assume that you have data from the 7th and 9th April 2003 for testing within your database.
Check this and get back to me.

John

 

[idd]

John,

I believe the code is correct too, I also have data for the 7th and 9th, of April 2003 which is identical in every way to the data for the other dates, but when I search for between dates it picks the data up when I search on a single date it wont pick up these two dates and a few others, but it picks up the 22nd April's data fine on a single date search.

I'm confused.

I have compacted and repaired the database but still no joy.

I'm open to alternative solutions.

to recap the problem is:

I have students who make enquiries the date of the enquirt is stored with enquiry/enquirer specific info. I want to be able to search for enquiries made on a certain day or range of days. which when passed through will be used in printing address labels for those students.

Any help Appreciated,
John I appreciate your help and time too.

Idd

 

[jrbarnett]

Idd

Yes, me again. A few ideas:
Firstly, and this should have absolutely nothing to do with the where clause:
Change the "ElseIf TwoDates =True" line to Else. This is because a boolean value can only have values True or False, so if it is not false it must be true.
This just makes it a tiny bit less complicated and confusing.

Just a quick thought as to why some queries work and others don't:
I'm not sure where you are based geographically, but this makes a difference to date storage in Access. If you are European, the dates are probably written in the dd/mm/yy format, whereas in the USA it is more likely to be mm/dd/yy (in Scandinavian countries the standard layout is yy/mm/dd, but I think we can rule that out).
If they are stored in a date/time field in the database, this should be sorted out by the database as the data is saved, with the actual format based on the System Short date layout unless otherwise specified but if it is in a text field, no such thing will be done.
Things to check therefore are:

1) Is the date is held as a date/time field?
Be sure to look at the actual table for this, not through forms which can put display formatting on to disguise the way of hiding the data.

2) Are the entries for 7th and 9th april different to others around that time that come out properly (in the underlying table(s))?

3) Do the entries for 7th or 9th April 2003 appear on a single date check for that date only?
If yes, then it is something to do with the Where clause in that second part.

If it turns out that the dates are held in a text field, a quick way to get this converted without restructuring the entire database would be to use the CDate function until you have time to do it properly. So try changing that second line to:

StrWhere = StrWhere & "between #" & CDate(Txt_start) & "# and #" & CDate(Txt_End) & "#"

Another way may be to use the >= and <= conditions instead of between, but I have never had that fail before. Try:

StrWhere = StrWhere & &quot;>= #&quot; & CDate(Txt_start) & &quot;# and <= #&quot; & CDate(Txt_End) & &quot;#&quot;

both with and without the CDate() calls and see if either make any difference.

In response to your comment about the hashes, they are used to delimit date values and should be recognised by the database engine as such in the same way that apostrophes are used to delimit text/string variables and numeric values don't need any at all.

John