Run a query between date ranges using SQL

[PALPETE]

I am currently using Access 2003, I have created a very simple database. I am trying to create a form that will run a date range. So far, I have been abble to accomplish this. I created the form with the name Range. Next I created a query. The date filed that I am searching for is title appointed. It is neccessary for me to run a range to include dates of appointment between to date, usually the begining and end of a month. But somethimes it is necessary for a quarter date range.

What I have located on the web has shown me the following

in the criteria cell:
[Forms]![frmRange].[txtStartDate]And[txtEndDate]

But this does not work. I tried:
[Forms]![frmRange].Between[txtStartDate]And[txtEndDate]

and that does not work either. Any help on this would be great. I do not know much about VB so I really and trying to do this just in Access.

Thank you in advance.

 

[wazz]

Between [Forms]![frmRange]![txtStartDate] And [Forms]![frmRange]![txtEndDate]

 

[PALPETE]

Thank ykou very much. I will try it.

 

[PALPETE]

I tried it and when I enter dates on the form then clik the command button it asks for Parameter value for forms!frmRange!txtStartDate and a Parameter value for forms!frmRange!txtEndDAte I am missing something then.

 

[wazz]

possibly you need:
Between [Forms]![frmRange]![txtStartDate].Text And [Forms]![frmRange]![txtEndDate].Text

 

[PALPETE]

still not working so I tried to write the query a different way:

SELECT [Client Name],[Cause #],[Attorney],[Lead Charge],[Appointed],[Class],[Court]
FROM [A and B Felonies]
Where ((( [A and B Felonies].Appointed) Between [Forms]![frmRange]![txtStartDate]And [Forms]![frmRange]![txtEndDate]))
UNION SELECT [Client Name],[Cause #],[Attorney],[Lead Charge],[Appointed],[Class],[Court]
FROM [Murder]
Where ((( [Murder].Appointed) Between [Forms]![frmRange]![txtStartDate]And [Forms]![frmRange]![txtEndDate]))
UNION SELECT [Client Name],[Cause #],[Attorney],[Lead Charge],[Appointed],[Class],[Court]
FROM [Child Molest]
Where ((( [Child Molest].Appointed) Between [Forms]![frmRange]![txtStartDate]And [Forms]![frmRange]![txtEndDate]))
UNION SELECT [Client Name],[Cause #],[Attorney],[Lead Charge],[Appointed],[Class],[Court]
FROM [C, D and MA]
Where ((( [C, D and MA].Appointed) Between [Forms]![frmRange]![txtStartDate]And [Forms]![frmRange]![txtEndDate]));

Still ask for parameters.

 

[wazz]

i would suggest making four separate queries, add the Appointed field to each with the criteria. test each to make sure they are working with the criteria-form.

do you try adding .Text ?

looks like the tables may need normalizing, but that's another story.

after the queries are each working, create the UNION query. you won't need any criteria. not completely sure this approach will work but i would start with separate queries.

 

[Brianwarnock]

In post 1 you said

I created the form with the name Range

but in the formulae you use frmRange

what is the name?

Brian

 

[boblarson]

Wazz:

You can't use .Text unless the control has focus and if you have two controls they each can't have the focus. It isn't the .Text part that is a problem.

PALPETE:
Why it keep asking for parameters is that you probably have misspelled something. Make sure that your form really is named frmRange (with no spaces or underscores) and your text boxes are really named txtStartDate and txtEndDate AND that the form is still OPEN.

Also, make sure that you have a space

Between [Forms]![frmRange]![txtStartDate]And [Forms]![frmRange]![txtEndDate]))

after the square bracket in red
Between [Forms]![frmRange]![txtStartDate] And [Forms]![frmRange]![txtEndDate]))

as each of the code you posted is missing that space.

 

[PALPETE]

Thank you all for your input. I have tried the .txt and came up with an error. I have not tried running them as seperate but will try that as well. as for Range v frmRange I was going off of an example I found online that listed the Name such as qryFormQuerySate and in the code it listed [Forms]![frmQueryState].[txtEnterState] So that is where I mimiced
[Forms]![frmRange] etc. Originally I had the spaces in there as an attempted to eliminate issues I took the spaces out. Again thank you.

 

[PALPETE]

I have gone back and forth with this I am just about ready to chuck it and manually right the query every time i need a date range. On my form the text boxes are unbound, does this make a difference?

 

[PALPETE]

Also when I put the dates in when it asks for Parameters it runs the query with the correct information.

 

[boblarson]

On my form the text boxes are unbound, does this make a difference?

It shouldn't. Can you post the database?

 

[PALPETE]

Unfortunately I cannot post it.

 

[boblarson]

Unfortunately I cannot post it.

Yes, that is unfortunate because we could have it figured out for you in about 20 seconds.

Wazz's post here was the correct way to reference it, IF the form and text box names are spelled correctly.

Not much else we can do to help you without being able to see what exactly it is you are trying.

 

[PALPETE]

I am going to make a dummy database with bogus info and post that.

 

[PALPETE]

Here is the dummy version

 

[PALPETE]

But with the dummy mbd it is only asking for a parameter for the end date

 

[boblarson]

Sorry, gotta have 4 other things. we need copies of the Excel files (it is okay to have them blank).

 

[PALPETE]

sorry about that here they are just having a moment I guess.