Input Query not displaying current entries

[RickHunter84]

Hello all,

I hope everyone is doing well in these crazy times.

I created a Input query to run based on a specific date. The input query retrieves the data from table X, the query works for any date except the current date(today), any ideas how to make it work for the current date?

Side note, the table where the query is getting the information from does have the data with current date entries, so I run the input query and it just doesn't display the information.

Any suggestions?
Thank you in advance.

Rick

 

[cheekybuddha]

Any suggestions?

Please post the SQL

 

[RickHunter84]

Here you go:

SELECT NotesTable.CommentDate, NotesTable.PN_FK, NotesTable.Customer, NotesTable.PriceQuoted, NotesTable.Contact, NotesTable.Comments
FROM NotesTable
WHERE (((NotesTable.CommentDate)=[Enter a Date to display]))
ORDER BY NotesTable.CommentDate;

Thank you in advance.

Rick

 

[jdraw]

Further to David's request, please show some sample data. First thought(just a guess) is that you may have/have not recorded time with the Date and that is causing the issue.
Just saw your response. What is your value for
[Enter a Date to display].

 

[RickHunter84]

Further to David's request, please show some sample data. First thought(just a guess) is that you may have/have not recorded time with the Date and that is causing the issue.
Just saw your response. What is your value for
[Enter a Date to display].

7/10/2020

I have other input queries that run from range, for example: Start date: 7/1/2020 - End date: 7/10/2020 - and it only shows data from 7/1/2020 to 7/9/2020

Thoughts?

Rick

 

[cheekybuddha]

Is CommentDate a DateTime (ie includes the time portion as well)?

Try:

SELECT CommentDate, PN_FK, Customer, PriceQuoted, Contact, Comments
FROM NotesTable
WHERE CommentDate >= Format([Enter a Date to display], "\#yyyy\-mm\-dd\#")
  AND CommentDate < Format([Enter a Date to display], "\#yyyy\-mm\-dd\#") + 1
ORDER BY CommentDate;

 

[Deleted member 147267]

7/10/2020

If that's what you're seeing in the table, and you have formatted the table field and/or the query field as short date (like mm/dd/yy) but it really contains various time values, then I'd say that is your problem. Your criteria defaults to 07/20/20 00:00:00. If the field contains 07/20/20 03:21:16 PM for example, you won't get 07/20 values.

Too late again!

 

[RickHunter84]

If that's what you're seeing in the table, and you have formatted the table field and/or the query field as short date (like mm/dd/yy) but it really contains various time values, then I'd say that is your problem. Your criteria defaults to 07/20/20 00:00:00. If the field contains 07/20/20 03:21:16 PM for example, you won't get 07/20 values.

Too late again!

Hello Micron,

Yes, that was the problem...Initially, I created the field without formatting it, so it created a mix long dates and short date entries after adjusting it.

I think I know how to fix it now, thank you for your input.

Regards,

Rick

 

[RickHunter84]

Is CommentDate a DateTime (ie includes the time portion as well)?

Try:

SELECT CommentDate, PN_FK, Customer, PriceQuoted, Contact, Comments
FROM NotesTable
WHERE CommentDate >= Format([Enter a Date to display], "\#yyyy\-mm\-dd\#")
  AND CommentDate < Format([Enter a Date to display], "\#yyyy\-mm\-dd\#") + 1
ORDER BY CommentDate;

Hello cheekybuddha,

Thank you for your help as well.

best regards,

Rick

 

[Deleted member 147267]

If adding a day doesn't work properly (you should get date values with time as 00:00:00) but you'll probably also pull in those that have time (e.g. 01:23:45) on the next day, which you might not want. I've done this before by using DateAdd and adding either 1439 minutes or 86399 seconds to ensure it was cut off just before midnight of the later date used in the criteria.

 

[cheekybuddha]

Yes, that was the problem...Initially, I created the field without formatting it, so it created a mix long dates and short date entries after adjusting it.

I think I know how to fix it now, thank you for your input.

Don't format your date field at the table level.

If you enter any of the following the field will have the same value:
(assuming US regional settings)
07/10/2020
10 July 2020
10 Jul 2020
7/10/20

However, when passing the value to SQL you must ensure it's in a format that SQL understands. The expression service will do its best to convert the date for you, but sometimes it doesn't hurt to coerce it explicitly!

 

[RickHunter84]

Hello,

I fixed the problem at the table level by formatting the default value like this: =FormatDateTime(Now(),2) // so every time there is a new entry in the table, the date gets added in this format: mm/dd/yyyy - Input queries are working perfect now.

Thank you both!

regards,

Rick

 

[cheekybuddha]

What are you regional settings? Are you in the US?

 

[RickHunter84]

What are you regional settings? Are you in the US?

Hey there,

Yes, in the US.

Rick

 

[cheekybuddha]

OK, good.

I'm surprised you need to format at the table field level (always dangerous IMHO), ...

but if what you have works ...

d

 

[cheekybuddha]

Ah, I see what you are doing!

An easier trick would be to set the default value for your field to:
=Date()

hth,

d

 

[RickHunter84]

haha, yes, sometimes we tend to over engineer the solutions

Rick

 

[cheekybuddha]

Obviously you lose the time the comment was entered. Is that important?

 

[RickHunter84]

No, its not for the purpose of the report its running, so date is the key in this case.

Thank you for your help!

Rick

 

[cheekybuddha]

Great!

d