Filter date for report from a popup form

[ozlander]

G'day,

Question #2 (btw, I wish this fantastic forum used a boolean search ... I tried and no results. Then again, may be it does and I just didn't use the correct terms ).

I am sure this question has been asked many times ... and if it has, I apologise, however, I cannot find the answer in simple enough terms).

1 I downloaded RptSampl.exe from Microsoft ... and ran it ...
2 The fourth last example refers to 'Setting Filter, FilterOn from a Popup Form'
3 I have adjusted it to my db for client, surname, etc ... and all works well (I even have the hang of select distinct ... lol)
4 However, when I tried a date query (short date format - set from table), I get a message, 'data type mismatch in criteria expression'
5 I have no idea how to fix it ... let alone what it really means ... so ...

-------->>>>> 6 Is there a chance to put in an 'or' or 'if' statement (or whatever it is that you guys use) in the 'event procedure' for a command button (wow, I am even learning some terms )?

7 Please make it simple

Point #6 is the question, btw

Thanks in advance
Oz

 

[ozlander]

wow ... 5 views and no replies ...I know the feeling ... yep ... :-D

 

[EmmaJane]

wow ... 5 views and no replies ...I know the feeling ... yep ... :-D

the problem is it Saturday and you don't get many on the boards over the weekend, only us fools who have to do six day weeks!!!!!!! give it a while, I know I looked so one of them was me but I'm sorry not that great myself and although I maybe able to offer some advise you are better waiting a bit problem even the being of the week.........

 

[ozlander]

lol
hahahhahaaaa....the message was too short ...limit of 10 characters ... oh well (btw, I reckon this matches that criterion ... lol)

 

[Rich]

If you post the code we might be able to help. I suspect the filter refers to textual criteria and not dates.

 

[ozlander]

Private Sub Command28_Click()

Dim strSQL As String, intCounter As Integer
'Build SQL String
For intCounter = 1 To 5
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
End If
Next

If strSQL <> "" Then
'Strip Last " And "
strSQL = left(strSQL, (Len(strSQL) - 5))
'Set the Filter property
Reports![Counsellor].Filter = strSQL
Reports![Counsellor].FilterOn = True
Else
Reports![Counsellor].FilterOn = False
End If

End Sub

 

[ozlander]

From searching the forum I suspect that the line

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "

in the above code may be the problem ... however, I don't understand what it means or how I could alter it so that it filters the date as well.

Could anyone help ... but please make it simple

Thank you in advance

 

[Mike375]

ozlander

I am not clear on what you are trying to get done.

Could you post something along lines of

I want xzy inserted into field abc if the date is greater or less than some date and only if the surname starts with an M

Mike

 

[ozlander]

Mike

I am not really sure how to express what I need ... but I'll have a go

I have a report and a form (filter) ... so that I can choose what I need to query ... the query part I seem to have under control. However, the button that sets the filter has the above code set in the event procedure. It seems to work for text based stuff but not for a date query ... I can see the dates in a dropdown box but when I select a date for the report I get an error message that reads, data type mismatch in criteria expression.

Could I put extra code in the event procedure so the filter works for text as well as date?

 

[Mike375]

Mike

I am not really sure how to express what I need ... but I'll have a go

I have a report and a form (filter) ... so that I can choose what I need to query ... the query part I seem to have under control. However, the button that sets the filter has the above code set in the event procedure. It seems to work for text based stuff but not for a date query ... I can see the dates in a dropdown box but when I select a date for the report I get an error message that reads, data type mismatch in criteria expression.

Could I put extra code in the event procedure so the filter works for text as well as date?

See if I have this right. You have a Report and for the basis of selection are dates selected from a combo plus some additional criteria

You should be able to have a query that supports your Report and that query has criteria based on the form that is open.

So on your form you have an OnClick on a label or command button and it opens and prints the Report.

What sort of criteria are you wanting to apply in addition to a selected date.

Mike

 

[ozlander]

I just worked out to compress the db ... so here it is ...
The form in question is the filterform

 

[Mike375]

I just worked out to compress the db ... so here it is ...
The form in question is the filterform

I can never get those attachments to work properly. But that will be OK.

Juts post up the fields and criteria you want. Don't worry about how you are doing it at the moment.

In other words you will either have a table or query that will support your Report... what is the criteria and different fields involved. Again, don't worry about describing what you have done so far. Lets get back scratch.

Mike

 

[ozlander]

Relevant Tables are:
ClientTable (relevant fields are ClientID, Surname, FirstName, Gender, Category)
SessionTable (relevant field SessionDate)
CounsellorTable (relevant field Counsellor)

Relevant Report is Counsellor (Headings are: Counsellor, Surname, FirstName, Gender, Category, SessionDate)

Relevant Form is FilterForm

From the above form I want to be able to query the report by either Counsellor/Surname/Gender/SessionDate/Category

Is this sufficient information?

 

[Mike375]

Ok. Do the form and Report have the same record source. For example does the same query support both the form and Report or are they different queries but based on the same table or are they based on two different tables.

From the above form I want to be able to query the report by either Counsellor/Surname/Gender/SessionDate/Category

What is your selection criteria on those fields and are all thoise fields part of the form.

When you say "I want to be able to query the report by either" do you mean that you will have different criterial for different times. In other words are there times where you might only apply criteria to [Counsellor] and other times where you might only apply criteria to [SessionDate] etc.

I am heading down to the shop for 30 minutes or so Got get something to eat.

Mike

 

[ozlander]

The report contains all the different fields from the various tables. The form has each of the queries which can be run at similar or different times (in other words, if I needed to run a query for counsellor and surname, I should get a report that lists one counsellor and one surname with the various session dates, etc

As sample of the form query source is:

SELECT DISTINCT [Counsellor] FROM CounsellorTable ORDER BY [Counsellor];

This changes depending on the [field] and the table from where the query source was obtained ... each query is 'group by'

 

[ozlander]

whoops... each query is sort ascending ... sorry

Shouldn't I be able to change the & bit (that's something to do with text, isn't it?) in the above code to # (which seems to be the bit for dates) as an additive to the code by some other means like 'elseif' or 'or'?

 

[Mike375]

When you say The form has each of the queries which can be run at similar or different times (in other words, if I needed to run a query for counsellor and surname, I should get a report that lists one counsellor and one surname with the various session dates, etc do mean mean that you have queries being opened (or Reports based on these queries) by a buttom or label on your form OR are you referring to changing the record source of the form with different queries.

Firstly, with quert criteria you should use And not & and also rereference the field. For example you would not have [Field]>= 100 And <=200. Instead you have [Field]>=100 And [Field]<=200. For text you use "Smith" or you might have "S*" and will be all the names that start with S or "*er" will be all the names like Miller and Summer etc

I am getting the impression thatg you might be doing too much with one query and as a result finsih with contradictory criteria.

I am probably not reading you right, however, from what I can see you have different reports running by clicking on a button on your form.

If that is the case, then I would have each query reference the form for criterial such as

[Forms]![MyForm]![Counsellor] And [Forms]![MyForm]![Surname]

So on a label (or button) that you might call Report 1 you only have a macro or code that opens and then prints the Report. The query supporting the Report does not need the same record source as the form.

I am sure we will get there

Mike

 

[ozlander]

There is only one report based on the fields drawn from the various tables. However, depending on what I query, I should be able to produce the watered down version of the report.

The button on the form (it says SetFilter) and when pressed the report is previewed (I don't wish to print it yet ... that will be another issue ... lol)

If I use text fields only it works extremely well so I thought I'd extend it to include the date ... that seemed logical to me ... lol ... so may be you are right a conflict

 

[Mike375]

There is only one report based on the fields drawn from the various tables. However, depending on what I query, I should be able to produce the watered down version of the report.

The button on the form (it says SetFilter) and when pressed the report is previewed (I don't wish to print it yet ... that will be another issue ... lol)

If I use text fields only it works extremely well so I thought I'd extend it to include the date ... that seemed logical to me ... lol ... so may be you are right a conflict

You should not need to confuse things by applying a filter as a query will do it for you. in other words the button only needs a macro or code that opens the Report and if want to preview it then the PrintOut action is not included.

What I would suggest is to make a copy of your data base. Then pick one set of criteria that you want and place them in the query with reference to the form such as [Forms]![MyForm]![DateField1]>=Date() or whatever the criteria will be.

Don't confuse things by using the filter you are operating from the button. If necessary, when the form is open bring the data base window to the front and then open the Report or just stick a simple macro ona label or button on your form. One action line all that is needed OpenReport.

Then make a copu of your Report and change the copy' record source to another query which has a different criteria based on the Form reference.

Also, keep in mind that if you place criteria on the same criteria line for different fields that results in an And search. If the criteria are placed on different criteria lines then that is an Or search

Mike

 

[ozlander]

btw, the earlier code (pasted on page #1) was for the button ... and it works except for the date field ... so I will take your comments onboard ... thanks