By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,067 Members | 1,283 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,067 IT Pros & Developers. It's quick & easy.

Using date on a form in a report

P: 41
I'm back again.

Of course, when I figure out one part of what I need to do, I need to make it better.

I'm trying to add an option to select a date range to report on, but it's not working how I think it should be.

Here's the peice of SQL that relates for my query.

WHERE (PagesHistory.ReviewStartTime) Between ([Forms]![ReportsForm]![StartDateTXBX]) And ([Forms]![ReportsForm]![EndDateTXBX]))

Did I make an error somewhere?
Jul 16 '09 #1
Share this Question
Share on Google+
6 Replies

Expert 100+
P: 1,287
Try this:
WHERE PagesHistory.ReviewStartTime BETWEEN
Format ([Forms]![ReportsForm]![StartDateTXBX], "short date") AND
Format ([Forms]![ReportsForm]![EndDateTXBX], "short date")
Jul 16 '09 #2

P: 41
Still getting a "Enter Paramater Value" box for the Start and End Date.

I tried displaying the date fields in a text box on a report, and it came up as "#Name?"

For what it's worth, here's the entire query:

SELECT Roles.LastName, PagesHistory.ReviewStartTime, PagesHistory.ReviewEndTime, DateDiff("s",[PagesHistory.ReviewStartTime],[PagesHistory.ReviewEndTime]) AS TimeSpent
FROM PagesHistory INNER JOIN Roles ON PagesHistory.RoleID = Roles.ID
WHERE (((PagesHistory.ReviewStartTime) Between Format([Forms]![ReportsForm]![StartDateTXBX],"Short Date") And Format([Forms]![ReportsForm]![EndDateTXBX],"Short Date")) AND ((PagesHistory.ReviewEndTime) Is Not Null) AND ((DateDiff("s",[PagesHistory.ReviewStartTime],[PagesHistory.ReviewEndTime]))>0));

Do I need to join anything to pull data from forms?
Jul 16 '09 #3

Expert 100+
P: 1,287
What's the source you're using for the text box on the report that shows #Name? Something is wrong in the way your are trying to point at the form data, but it could be anything from the name of the box being misspelled to the form not being open at the time.
Jul 16 '09 #4

P: 41
Yeah I don't really get why it's not working in the text boxes in the report

=Format([Forms]![ReportsForm]![StartDateTXBX],"Short Date")

is what I'm using
Jul 16 '09 #5

Expert 100+
P: 1,287
Try in the immediate window
? Format (Forms!ResportsForm!StartDateTXBX, "Short Date")
or put a button on your form and have it do
MsgBox Format (Forms!ResportsForm!StartDateTXBX, "Short Date")
and see what you get. I suspect that something is simply being referred to by the wrong name.
Jul 16 '09 #6

P: 41
Another bonehead mistake on my part lol

I used your idea of throwing in a msgbox and it told me that ReportsForm didn't exist... which I know it does!'s a subform of a form I wasn't including in the command >.<

Anyways, problem solved, thanks again, ChipR!
Jul 17 '09 #7

Post your reply

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