filter problem!

[geregs]

Hi All

I had some problems with filters in access XP but with your help I solve it, but I have another problem with that now. Until now, I used the filter for text fields and number fields and it is working ok, but now I wont to use the filter in date field but it is not working, so, I need help.
The format of the date field is short date.
This is the code for the text field:

filtika = filtika & " and [myfieldname]= '" & Me!FilterTextBox & "'"
If Left(filtika, 5) = " and " Then
filtika = Mid(filtika, 5)
End If
Me.Filter = filtika
Me.FilterOn = True

This is the code for the number field:

filtika = filtika & " and [myfieldname]= " & Me!FilterTextBox
If Left(filtika, 5) = " and " Then
filtika = Mid(filtika, 5)
End If
Me.Filter = filtika
Me.FilterOn = True
And now I need the code if the myfieldname is date field (short date)!

Thanks in advance!

 

[CyberLynx]

When dealing with Text strings, you enclose the text within apostrophes ( ' ) when passing these strings to Filters, Queries, and Functions which retrieve data from a Table.

filtika = filtika & " and [myfieldname]= '" & Me!FilterTextBox & "'"

For Numbers you use nothing, unless a variable is used and needs to be converted.

filtika = filtika & " and [myfieldname]= " & Me!FilterTextBox

For Dates you need to use the Number symbol ( # also known as the pound symbol):

filtika = filtika & " and [myfieldname]= #" & Me!FilterTextBox & "#"

.

 

[geregs]

Almost OK!

Thanks CyberLynx!!!

I changed the code like you wrote me but it is working only for the date 01/01/2008, for other dates it is not working... I do not know why???

 

[CyberLynx]

Ensure that the Date field you are referencing in Table is in fact a Date/Time Data Type.

You may need to format your TextBox to provide the same format and what is used in Table or at the very least Format the retrieved date from TextBox to match that in Table. It's hard to tell what is going on with your DB from here.

I've seen different variations work for different situations:

filtika = filtika & " and [myfieldname]= #" & Format(Me!FilterTextBox, "Short Date") & "#"

filtika = filtika & " and [myfieldname]= #" & CDate(Me!FilterTextBox) & "#"

.

 

[geregs]

still filter problem....

Thanks CyberLynx, I tried everything that you wrote to me but in the end the filter is working only for 01/01/2008 and for 22/02/2008, I do not know why!!!
So here is the base of the problem so if you have a little time for checking it, I would be very happy and thankful.
Thanks in advance!

 

[Rabbie]

Thanks CyberLynx, I tried everything that you wrote to me but in the end the filter is working only for 01/01/2008 and for 22/02/2008, I do not know why!!!
So here is the base of the problem so if you have a little time for checking it, I would be very happy and thankful.
Thanks in advance!

I have played a little with your code and stepped through it

in this Section

Case "="
                    filtika = filtika & " and [Datum]= #" & Me!TextFiltera & "#"
                    If Left(filtika, 5) = " and " Then
                    filtika = Mid(filtika, 5)
                    End If
                    Me.filter = filtika
                    Me.FilterOn = True

filtika is keeping its value from the previous call so you finish up with a filter of DAtum = #1/1/2008# and Datum = #22/2/2008#

This will always be false so you will need to amend your code to evaluate the correct filter. I would suggest you put in a debug.print filtika before you set the filter so you can see what is happening

 

[CyberLynx]

All looks good within your Sample DB and will function as planned. The reason you are having difficulty is because there are two different Date formats in play here.

The Datum date field within Table is expecting a US Date format (mm/dd/yyyy) and your computer system's Date Format is of something different (dd/mm/yyyy). Even though it appears as though the Format is what is intended...it really isn't. When you open a Table in Datasheet view, Date fields are displayed to you within the date Format as set forth within the Regional Settings of your computer system or by way of Format property of the Field itself but this is for your viewing pleasure only. It doesn't necessarily mean the Date was saved to table in that Format.

Date data is saved to Table according to the Country/Regional Settings of your computer system. If a computer system initially run the Database and it contained a Regional Setting of English (US) and date data was saved to table, then you take the DB home and run it on a computer system with a Regional Setting of say, English (Canada), you will run into problems.

To quickly remedy your particular situation, either set your computers Regional Setting to English (US) so that the Date is (mm/dd/yyyy) (Control Panel | Regional and Language Options) or utilize the Format() Function within your code to match the saved Date format. For example:

filtika = filtika & " and Datum=#" & Format(Me!TextFiltera, "mm/dd/yyyy") & "#"

On another note, the code line:

If Left(filtika, 5) = " and " Then filtika = Mid(filtika, 5)

should be:

If Left(filtika, 5) = " and " Then filtika = Mid(filtika, 6)

It was prefixing your Filter with a Space.

Also, at the start of your Select Case, enter the code line: Me.Filter = "" so as to clear the Filter property for every new button hit.

.

 

[Rabbie]

Cyber, I agree with your comments on date format but the code as written will still give funny results because it is doing a concatenation of different results. This will be fine when a filter such as [datum] > #1/1/2008# and < #2/28/2008# but will be nonsensical if both operators are set to = for example. Just set breakpoints in the code and step through it to see what I mean.

 

[CyberLynx]

I agree with you Rabbie. Your Post was not in place when I first initiated my response.

I'm not familiar with the language used within the Database (be interested to know though). I do see the AND operator within code but don't see anywhere it can be implemented unless the Filter button is hit a Second time. I'm not sure if this is the intent of the developer.

For the very reason you have stated is the reason I have also indicated within my post that the Filter should be cleared upon each button hit (Me.Filter = "").

If the developer wants to utilize two specific Dates within the Filter then the Operator would need to change to the OR operator.

[Datum] = #01/01/2008# OR [Datum] = #02/28/2008#

will function just fine.

.

 

[geregs]

Thankssssssssss!!!!!


Hi All!
Thanks for All the replays, it SOLVED my date problem!!!! So, IT WORKS!!!
Thanks again! Best wishes to all!