Between dates query (2 Viewers)

[dzelmej]

Im trying to build form with two calendar controls which asks query for records between two dates.
Calendar control works fine, no problem with that, but my query isnt.

What im trying to do:
I have a table with records, each record has field with date of creation (for example 2007.11.25.) formated as date/time (im working with latvian local settings)

In my form, I have a two text fields which referes to each calendar control, so in query (whith is trigered by comand button) i have formula like this:

Between [Forms]![Form1]![text5] And [Forms]![Form1]![text7]

Everything seams to be fine (for me ) but if im searching dates between 2007.01.01. and 2007.03.01. im getting not only correct records between those dates, but also random records from last year.

I have tryed many diferent formulas, but nothing is working, Im stuck in this.

Ofcourse, my problem is that Im not good with VBA I dont understand it at all

Sorry if someone allredy posted solution of my little problem somewhere in Jungles of these Treads

 

[boblarson]

Format the dates within your query to US date format (pesky little detail given by Microsoft). So, if you format your text boxes to show the dates in US format (or have hidden ones that refer to the visible ones).

 

[dzelmej]

my text box and dates in query are formated as Shortdate... and in prew. are shown corectly, so where can I find US date format, I cant change my local settings. is there any custom date format propreties that I dont know about?
where can I change it?

 

[boblarson]

Your settings are regional, so that's why they display like they are. Try setting the formats to "mm/dd/yyyy" instead of short date (if your users are used to seeing the other format, I would create two hidden text boxes, with their formats set thus, and set their controlsource to be the appropriate text box. And then reference the two hidden boxes in your query.

 

[dzelmej]

ok, I get it, but still, that doesnt solve the problem, it still returns random 2006 records.
strange

source table is linked from remote server, but that cant be the problem... so what else...
"Between" doesnt work with dates? Or maybe i must use "#" some place?

 

[boblarson]

Can you post a screenshot of the query in design mode?

 

[rainman89]

between works with dates!

 

[dzelmej]

screenshot with design mode is attached to my first post. (only diference is date format, now it is US)


but there is nothing to see... as usual - Between value1 and value2

 

[bobo962]

Records between 2 dates

Hi,

I am new in this forum and new to VBA and MS Access as well!
I have one problem (similar to dzelmej's) so would be very gratefull if someone help me!

Among others I have date range (2 table/form fields (StartDate and EndDate with Data Types-Date/Time) and RoomNumber field (data type-Number) for reservation system aplication. Both (start and end) dates and RoomNumber have to be entered separately as input parameters or through pop-up dialog form, thereafter to execute query and to return availability only for specific records (Name, Surname etc., StartDate, EndDate, RoomNumber) which
match mention criterias (which fall between 2 entered dates). Looks simple, but "Between [StartDate] And [EndDate]" or ">=[StartDate] AND
=<[EndDate]" doesn't work well (probably would work if it is only one date field), but as soon as you put dates which does not fall exactly
between two already entered ranges in table, results are faulty...I have tried query which calls another query, applied filters etc, with no
success! Any suggestions would be appreciated!

Best regards!

 

[Jon K]

Hi Bobo,

See the sample database, which demonstrates the criteria that we can use in a query to determine which rooms can be booked for a range of days in a reservation database.

You can open the form, enter a start date and an end date of a potential reservation, and click on the two buttons to view the rooms available for booking as well as the rooms already reserved in that period.

The two buttons respectively run the two queries: "Rooms Reserved" and "Rooms Available". The latter query is based on the former.

The criteria used in the "Rooms Reserved" query is:-
Booking.StartDate<=Forms!frmSearch!txtEndDate And Booking.EndDate>=Forms!frmSearch!txtStartDate


The criteria can also be modified to deal with reservations in a hotel environment where a room can be booked on a date that is the last day of an existing reservation e.g.

Booking.Arrival<Forms!frmSearch!txtEndDate And
Booking.Checkout>Forms!frmSearch!txtStartDate

Hope the sample helps.
.

 

[dzelmej]

its getting frustrating. I cant solve this little problem with between dates, it just dont work, what am I doing wrong? :/

 

[rainman89]

can you post your db(stipped down of course) so we can take a look at it?

 

[dzelmej]

im workin on it its hudge, so it takes time to extract some data, i have linked tables, it not so easy as it seams at the first moment

 

[bobo962]

Hi Bobo,

See the sample database, which demonstrates the criteria that we can use in a query to determine which rooms can be booked for a range of days in a reservation database.

You can open the form, enter a start date and an end date of a potential reservation, and click on the two buttons to view the rooms available for booking as well as the rooms already reserved in that period.

The two buttons respectively run the two queries: "Rooms Reserved" and "Rooms Available". The latter query is based on the former.

The criteria used in the "Rooms Reserved" query is:-
Booking.StartDate<=Forms!frmSearch!txtEndDate And Booking.EndDate>=Forms!frmSearch!txtStartDate


The criteria can also be modified to deal with reservations in a hotel environment where a room can be booked on a date that is the last day of an existing reservation e.g.

Booking.Arrival<Forms!frmSearch!txtEndDate And
Booking.Checkout>Forms!frmSearch!txtStartDate

Hope the sample helps.
.

Hi Jon K.,

This is very interesting concept and it seems to be working!
A am very gratefull for your help!!!

Thanks again and if you need anything pls do not hesitate to contact me!

Best regards,
Bobo

 

[dzelmej]

I fixed it, the problem was that my linked table was showing corupt data, after update, random 2006 year records is gone.

 

[rainman89]

Glad to hear that your solved your problem! corrupt data explains alot!

 

[tbarkdull]

Between dates with open (0) fields

I have a quiery built by someone else that I think is the root of my problem. The query runs and selects Officers of an Insurance Company based on the dates in a table stating when they were officers. The problem is one of the date (enddate) is blank since he/she may be a current officer. The query reads ">[RPT_NAIC_AF1]![BeginDate] And <=[RPT_NAIC_AF1]![EndDate]" It is not returning all records after the start date which has no enddate. If I change the "And" to an "Or" I get the records I want, and a lot more so I know the problem lies here. Help please! I am a novice user but willing to learn.

 

[bobo962]

I have a quiery built by someone else that I think is the root of my problem. The query runs and selects Officers of an Insurance Company based on the dates in a table stating when they were officers. The problem is one of the date (enddate) is blank since he/she may be a current officer. The query reads ">[RPT_NAIC_AF1]![BeginDate] And <=[RPT_NAIC_AF1]![EndDate]" It is not returning all records after the start date which has no enddate. If I change the "And" to an "Or" I get the records I want, and a lot more so I know the problem lies here. Help please! I am a novice user but willing to learn.

Read post 10 of Jon K. in this thread! His example is working OK!

 

[tbarkdull]

Between Dates query

I did read JonK's post. The solution is what I am using now. I need a way of dealing with a enddate field that is blank (null). As a novice user, if there is something I'm missing, please explain.

 

[rainman89]

what if u set the default to todays date which would be =Date()? that way it wouldnt neccesarily be null, or are u trying to do this for a single day?