Between dates query

[Gaccesses]

Hi all,

Side note: From Datapigs, the instructor shows that you should probably program any queries that will be using "AND" within the criteria. I wouldn't know how to code in VBA in order to use a query.

My level is beginning intermediate, so I still need a lot of things spelled out to me.

PLEASE HELP!

I have a search form. Within the search form the user can search for cars sold in a given period of time.

I have a table.. which lists the dates that each car was sold.

DateSold<--column name

The query I have created is not working properly. I keep getting the error code 3071- The expression is typed incorrectly, or it is too complex to be evaluated.


In my query I have - DateSold from a cars table. In the criteria row, I typed Between Datesold.beg AND Datesold.end. I want for the query to search within the column DateSold and return all cars sold within a specific period.

Example

DateSold
1/1/2001 A
1/2/2001 B
3/4/2001 C
3/8/2001 D
3/9/2001 E
3/10/2001 F

if i type in i want to search between 3/1/2001 to 3/12/2001------ CDEF should pull up.

 

[Mr. B]

First, you need to get your query working for you. To do this, assuming that your query is just based on the cars table, in the criteria row of the "DateSold" field in your query, type:
Between 1/1/2010 And 3/8/2001

Try running your query. You should see only records where the date in the DateSold field are between the two dates you have entered in the criteria. Now, try changing the dates in the query and test your query.

When you have your query working using this specifically typed criteria you can then have this query look for these two date in your search form. You would have two text boxes formatted as Short Date on your form. I would name these two text boxes "txtStartDate" and "txtEndDate" respectively. Then in your query you can use the Builder (put your cursor in criteria for the DateSold and right click and from the menu select the "Build ..." option. You can use the Builder to identify the form and the two text boxes that will provide the dates that you have previously been typing in. When you have specified the form and the two text boxes for the critieria, you should have something that looks like the following in the criteria for the DateSold field.

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

With this in your criteria, save your query. open your form in form view and enter an appropriate date in the Start Date text box and another appropriate date in the End Date text box. When you now open your query, you should see records displayed where the DateSold is between the dates you provided on your search form.

 

[Brianwarnock]

If as is suggested in your post, you are entering the dates in a forms controls then the query needs to refence these in the form

Forms!formname!controlname


Brian

I see mr B beat me

 

[Mr. B]

Brian,

I think we were responding to this one at about the same time.

I could not tell that the OP was actually using the "search form" so I just decided to try to provide

My level is beginning intermediate, so I still need a lot of things spelled out to me.

at least some of what is being asked for.

Gald you jumped in.

 

[Gaccesses]

While I want to move on and take your advice B and Brian. I continue to get an error on the vba code written on a button click which would activate the date query.

The error is trying to debug: DoCmd.OpenQuery "GeneralSearchQ", acViewNormal

I dont see anything wrong with it because I was able to use this before.. so I am stuck on trying to figure that part out for now

 

[Gaccesses]

OK , I should have stated this before.

within my query I have:

Forms![SoldCars].[datesoldstart]
Forms![SoldCars].[datesoldstop]

so these are being referenced.

what is happening: instead of the search form I created being used as a query.. the query creates pop ups for me to enter the date sold range. Any tips?

Mr. B.. query is working properly if I enter direct numbers

 

[Gaccesses]

None of your tips seem to be working. Anyone else out there able to help? Still encountering the same issue. The error message still points to the command to open the query

 

[Mr. B]

Is "datesoldstart" the name of the textbox control where you are entering the starting date?
And like wise the "datesoldend"?

Can you post your query so we can see how you have it structured?

 

[Gaccesses]

Query Builder

Car VIN Car Color Check Up Status DateSold
CarsSold CarsSold CarsSold CarsSold


Criteria for each has a like forms![carssold][GENERICNAME]

as for column datesold


between forms![carssold].[datesoldfirst] AND forms![carssold].[datesoldlast]

 

[Brianwarnock]

I see that the names have changed

BTW you will notice that Mr B and I use ! Not . In the statements.

Pat Hartman wrote this in response to a query on this subject, ok a more complex situation but you get the message.


You need to be careful with bangs (!) and dots(.), they are not always interchangeable. In general dot is used when the following property/object is native to Access and bang is used when the property/object is something you defined. That's why the proper reference format is:
Forms!yourformname.Form!yoursubformname!yourcontrolname


Brian

 

[GaryPanic]

Hi

Ok
start at the beginning

date fields on your search form
from dd/mm/yyyy to dd/mm/yyyy or if your using us format mm/dd/yyyy

usually if ithe qry worksd outside of a form then the referencing to the forms is wrong - delete the critia and start again Forms! then the name of the form (I cut and paste the name of the form) ! then the name of the field - check
the name of the fields on your forms - again I cut and paste - ensuring that everything is correct - in the naming

 

[Gaccesses]

Sometimes a basic solution is to erase your original textboxes and create new ones. I had done everything right coding wise, but a bug in access got in the way.