Query Parameter date range question

[Ms Kathy]

I'm using a form to select a date range. Using the following, and entering start date of 6/1/14 and end date of 7/1/14 I would expect to pull the records with a date of 7/1/14; however it doesn't. I have to enter and end date of 7/2/14 to pull 7/1/14 records. Any advise please?

>=[Forms]![F_Transaction_Date_Range]![txtStartDate] And <=[Forms]![F_Transaction_Date_Range]![txtEndDate]

 

[bob fitz]

Try:
Between [Forms]![F_Transaction_Date_Range]![txtStartDate] And [Forms]![F_Transaction_Date_Range]![txtEndDate]

 

[Ms Kathy]

I tried the "between" first with the same results. Then I explored the world wide web and half a day later found the >= scenario. But I get the same results no mattter which I use . . .

 

[bob fitz]

I suspect that the field you are setting the criteria for holds a date and time value.
Try something like:
Expr1: Format([YourDateFieldName],"mm/dd/yyyy")
and then use the Between expression that I suggested earlier.

 

[Ms Kathy]

I entered this:

Expr1: Format([Trans_Date],"mm/dd/yyyy")Between [Forms]![F_Transaction_Date_Range]![txtStartDate] And [Forms]![F_Transaction_Date_Range]![txtEndDate]

And got an error message "invalid . (dot) or ! operator or invalid parenthesis

 

[bob fitz]

Use:
Expr1: Format([Trans_Date],"mm/dd/yyyy")
In the first line of the query designer to create a new column.
Use:
Between [Forms]![F_Transaction_Date_Range]![txtStartDate] And [Forms]![F_Transaction_Date_Range]![txtEndDate]
as the criteria for that column.

 

[Ms Kathy]

hmmm so it comes up blank now.

I had put that "between . . . " criteria in the TransDate column of the query. But I removed it from there and placed it as you indicated. I'm not sure where I'm going wrong.

(I'm pretty fuzzy now - it's 92 degrees in this office and the file server fan is churning away like a jet engine!)

 

[bob fitz]

Can you post the SQL statement of the query

 

[Ms Kathy]

not sure what you mean? how do I do this?

 

[bob fitz]

Not sure which version you are using, so it is difficult to give precise instruction.
Wherever you click to change to Design view or Datasheet view, look for SQL option. After selection you should see a box with text in it. It will start with SELECT.......
Copy all that text and paste it in your post.

 

[bob fitz]

not sure what you mean? how do I do this?

See if this helps: http://www.techonthenet.com/access/queries/view_sql2007.php

 

[Ms Kathy]

m using 2007. This is what I see from design view (this is half the viewing area - I can take a shot of the rest of it if that's what you're asking for)

 

[Ms Kathy]

that link helped! here you go:

SELECT T_Transactions.Trans_AutoID, T_Transactions.Trans_Customer, T_Transactions.Trans_Weight, T_Transactions.Trans_PricePerPound, T_Transactions.Trans_MiscCharge, T_Transactions.Trans_MiscCharge_Description, T_Transactions.Trans_Date, CCur([T_Transactions]![Trans_Weight]*[T_Transactions]![Trans_PricePerPound]+[T_Transactions]![Trans_MiscCharge]) AS [Trans Total], Format([Trans_Date],"mm/dd/yyyy") AS Expr1
FROM T_Transactions
WHERE (((CCur([T_Transactions]![Trans_Weight]*[T_Transactions]![Trans_PricePerPound]+[T_Transactions]![Trans_MiscCharge]))>0) AND ((Format([Trans_Date],"mm/dd/yyyy")) Between [Forms]![F_Transaction_Date_Range]![txtStartDate] And [Forms]![F_Transaction_Date_Range]![txtEndDate]));

 

[bob fitz]

Have you looked at the link I posted in post #11

 

[Ms Kathy]

I am required to leave this office at 4:30 (which is about 5 min.) - unable to work on this from home. I will return tomorrow morning and look for your response then. (I was able to copy the SQL statement for you - in post #13). Thank you kindly!

 

[bob fitz]

I am required to leave this office at 4:30 (which is about 5 min.) - unable to work on this from home. I will return tomorrow morning and look for your response then. (I was able to copy the SQL statement for you - in post #13). Thank you kindly!

OK. Sorry, I missed your SQL post. Will respond with some thing for you to try but will not be able to see your response until I get home tomorrow evening at around 7.30 pm. It's now 9:30 pm so it will be late in your day but I will be back

 

[bob fitz]

I have looked at your SQL statement which seems OK. If you can you confirm that the form holding the dates to be used in the criteria is open and has dates entered in the appropriate controls and it is still not working perhaps you could attach a copy of your db, preferably in Access 2003 mdb format. Just the table, form and query with a few dummy records.
EDIT:
Can you also confirm that the data held in the field called "Trans_Date" has both Date and Time.
Can you also comfirm that you use the American date format (mm/dd/yyyy).

 

[Ms Kathy]

Can you also confirm that the data held in the field called "Trans_Date" has both Date and Time.
Can you also comfirm that you use the American date format (mm/dd/yyyy).

The Trans_Date field in the table is set to date/time and the format is "short date" (mm/dd/yyyy). Also, I confirmed that the form is open and has dates in it. I've attached the db.

I work a half day today and then I'm not back in the office until Tuesday morning 8am (US eastern daylight time).

Thank you kindly for all your help!

 

[Ms Kathy]

I came across something in my searches today and thought I'd give it a try - just adding the +1 at the end. Turns out it works. I placed this criteria in the Trans_Date column in the query and deleted the Expr1 column. Unless you can see this criteria being an issue down the road I'm going to go ahead and use it as it is. Thank you so very much for your time!

Between [Forms]![F_Transaction_Date_Range]![txtStartDate] And [Forms]![F_Transaction_Date_Range]![txtEndDate]+1