473,320 Members | 1,794 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Filter Form By Date Criteria

68
I have a form that pop up on the sessions form. i use the below code to filter the sessions form by the strat date of the course but it seems it is not giving any result. I get only empty fields

Expand|Select|Wrap|Line Numbers
  1. Private Sub Toggle10_Click()
  2. [Forms]![Enrollment].Filter = "sessions.[start date]=" & StartDate
  3. [Forms]![Enrollment].FilterOn = True
  4. End Sub
is there any problme with this code?
Please adivse
Jul 23 '09 #1
5 15643
ChipR
1,287 Expert 1GB
If [start date] is a date field, then you must surround it by # signs.
Expand|Select|Wrap|Line Numbers
  1. [Forms]![Enrollment].Filter = "sessions.[start date] = #" & StartDate & "#"
Jul 23 '09 #2
ahd2008
68
thanks dear.

it works now but there is a prolbme i have three session whith the following start dates:

1-10/07/2009
2-17/07/2009
3-14/12/2009

I typed the second date and then the third in the StartDate to filter sessions form and it worked. but when i typed the first date 10/07/2009 it gave me empty fileds.

Do you know what is the problme?

Appreciate your help
Jul 23 '09 #3
ChipR
1,287 Expert 1GB
This is from another forum:
The default Access SQL date format, regardless of locale, is mm/dd/yyyy. If you use an invalid date format, it will 'helpfully' try to convert that to a valid date for you.

So, if you use '30/09/2008', it will recognize you're using dd/mm/yyyy, and convert it appropriately. However, a value like '10/01/2008' is a valid mm/dd/yyyy value to begin with, so it will not be converted, and stored incorrectly in case you actually meant dd/mm/yyyy....

The solution is to always convert your date values to a mm/dd/yyyy string prior to using them in Access SQL statements. You have to be a bit careful here, as using VBA date format masks may not work entirely as you'd expect on non-US locales (e.g. 'helpfully' interpreting "mm/dd/yyyy" as "the localized short date format"), so please test carefully using your particular Access/VBA version.
Jul 23 '09 #4
missinglinq
3,532 Expert 2GB
Access is really biased towards using the American date format of mm/dd/yyyy and you're not using that. It can cope with 17/07/2009 and 14/12/2009 because they can only be interpreted one way: July 17, 2009 and December 14, 2009, but 10/07/2009 can be interpreted as October 7, 2009 or July 10, 2009, and Access gets confused.

Allen Browne has an excellent paper on the subject and gives some suggestions, I believe, on handling the problem at

http://www.allenbrowne.com/ser-36.html

Linq ;0)>
Jul 23 '09 #5
ahd2008
68
Thanks gents good stuff to learn.

I'm glad to learn this and appreicate it
Jul 24 '09 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: CSDunn | last post by:
Hello, I have a problem with field filtering between an Access 2000 Project form (the application is called CELDT), and the report that shows the results of the filter. Both the form and the...
3
by: Afton | last post by:
I would like to make a form that filters a report by Supervisor and by starting and ending date. I have the supervisors in a combo box, and that works. However, I do not know how to code to let...
4
by: Nhmiller | last post by:
This is directly from Access' Help: "About designing a query When you open a query in Design view, or open a form, report, or datasheet and show the Advanced Filter/Sort window (Advanced...
1
by: lorirobn | last post by:
Hi, I have a report that works just fine. Now I would like to add the capability to choose selection criteria to limit what is displayed. I created several reports that do this, but they used...
6
by: fonzie | last post by:
Is there any way to include two unbound text boxes (for a start date and end date) to a filter-by-form? The users may want to filter by several different fields and they may want to include a date...
5
by: Ron S | last post by:
After days of searching I finally an example that would work with my application, the only problem is after entering all of the code it is not working. Would someone be kind enough to take a look at...
2
by: jcf378 | last post by:
hi all. I have a form which contains a calculated control ("days") that outputs the # of days between two dates (DateDiff command between the fields and ). However, when I click "Filter by...
9
by: dee | last post by:
I'd like to filter by the following criteria: left(LeadDisposition,3) = "Sit" AND Appt_Date = Text767 I have no idea how to do this. Appreciate help.
1
by: Cara Murphy | last post by:
Hi There! Hoping you are able to help me with a filtering scenario: I have created a pop up form (Filter) to filter a report (Open Issues), which currently has a number of combo boxes which...
5
by: DAHMB | last post by:
Hi all, Using Access 2007 I have a report called Sunday School Attendance based on a Query called qryAttendance the query is as follows: SELECT tblSundaySchoolAttendance.StudentID,...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.