Date with Function After It

[KurtW]

I am trying to write a query returning results for a date period. This is sample data:



10/17/2008 8:29:37 PM

I am using the following Criteria:

Between [enter begin date] And [Enter End Date]

The time after the date is killing the query. How can I include a wildcard after the dates. I do not care about the times. I tried"

Between [enter begin date] And [Enter End Date] And like "*"

However, that did not work. Thanks for your help.

Kurt

Bah...sorry for the title...it should be Date with Wildcard after it.

[orange]

What exactly is this
10/17/2008 8:29:37 PM

And what were the entered Begin and End Dates?

What are you trying to do, in plain English?

[KurtW]

This is sample data:

10/17/2008 8:29:37 PM

I am trying to do a query that returns everything on a specific date. I want the query to ignore the time.

I have used this many times successfully before this query "Between [enter begin date] And [Enter End Date]"

The time after that date is causing the query to return nothing at all. If I enter the dates, I want it to return the above record/sample data.

Kurt

[orange]

Ok. One step at a time. How did the Time get on the test data?

Using your test data sample, what exactly did you enter in the begin date and enter Date.

I remind you --- readers only know what you tell/write. So be very explicit and you'll get focused answers.

[KurtW]

First, I am linking to a SQL database via access. I do not have access to the SQL Server itself. The application or sql is automatically populating both the date and time. I am actually querying a "log" table that audits everything users do in the application. Everything in the table is generated by the "system" so to speak. Users cannot directly write to this table. The table looks like this:

UserID	TableChanged	ChangeDate	Action	KeyValue	KeyData	SequenceNumber	ChargeUid	FileNumber	DefendantNumber	HasAuthUserList	DateSentToSharingSite	LocalComputerName
JOEH	TblCtUserProfile	7/11/2008 1:36:49 PM	Insert	AUDIT	AUDIT	1				0
JOEH	TblCtUserAuthorizations	7/11/2008 1:36:50 PM	Delete			2				0
JOEH	TblCtUserAuthorizations	7/11/2008 1:36:50 PM	Insert			3				0
JOEH	TblCtUserProfile	7/11/2008 1:37:41 PM	Insert	BILLL	BILLL	4				0
JOEH	TblCtUserAuthorizations	7/11/2008 1:37:42 PM	Delete			5				0
JOEH	TblCtUserAuthorizations	7/11/2008 1:37:42 PM	Insert			6				0

I am trying to pull from the ChangeDate column. Between [enter begin date] And [Enter End Date]. I entered 7/11/2008 twice. Normally this works with no problem. However, the time follows the date. I figured that I could use a wild card after the date. For example,

Between [enter begin date] And [Enter End Date] And like "*"

This did not work either. I only need to query the date. I want me query to ignore the time. I basically want to do something similar to "JHe*" but with the date and time but over a range of dates. (Hopefully my example did not confuse the issue.) The time at the end of the date is throwing my query off.

Kurt

[June7]

Suggest you extract the date part with an expression and apply filter to this constructed field.

ChangeDateAdj: CDate(Format([ChangeDate], "mm/dd/yyyy"))

I NEVER use dynamic parameter input popup - cannot validate user input.

LIKE and wildcard really only good for text values, not number or date.

[orange]

You could try the following to remove the Time part of ChangeDate

CDate( format(ChangeDate,"Short Date")) Between [Enter Start] And [Enter End]

[KurtW]

Sorry for the slow reply. This dropped off my RADAR, but I am messing with it again. I tried your two solutions but neither worked. I am not sure I understand your solutions. I am trying to figure the correct format that I need to enter in the yellow highlighted area below.



Kurt

[June7]

We are suggesting you create a field with an expression: CDate(Format([ChangeDate], "Short Date"))

Then apply filter criteria to that constructed field: BETWEEN [Enter Start Date] AND [Enter End Date]

[CJ_London]

the problem with

10/17/2008 8:29:37 PM


is that it is 'later' than 10/17/2008 - 8 hours and 29 minutes later which is why your 'between' isn't working

Orange's solution will work, or you can use DateValue(ChangeDate)

create a new column in your query and put 'CDate( format(ChangeDate,"Short Date"))' on the field row and 'Between [Enter Start] And [Enter End]' in that column criteria. Untick the show option if you don't want it to appear in your query

[KurtW]

I was able to get the query to work using the following: Date: Format([EnterDate],"mm/dd/yyyy")

I entered a date range in my query after being prompted of 04/13/2015 and 04/17/2015

It returned values within the above date range, but it also returned values between 04/13/2014 and 04/17/2014. Anyone know why this is happening?

Thanks for the help!

Kurt

[KurtW]

I use the following to try and convert the string to a date:

Cdate(mid(Date,4,2)+"/"+right(Date,4)+"/"+left(Date,2))

The Date field looks like the following:

01/25/2015

It returns results where the entire column looks like the following:

#error

Thanks for your help.

Kurt

[CJ_London]

not clear what you are saying.

Are you saying you have a string '01/25/2015' which you want to convert to a date or a string that looks like what?

Also Date is a reserved word, if you are really using it, change the name to something else

[KurtW]

Yes, the string is '01/25/2015'. Obviously this is just one value in the column, which is named "Date" (column). I need to convert '01/25/2015' to a date so that it can be queried. Again, "Date" is the column that contains the strings that need to be converted to Access recognized dates. If I understand you correctly, my column should not be labeled "Date" because it is a reserved word. I changed the column name and that did not fix the problem.

My focus or problem is the following:

Yes, the string is '01/25/2015'. Obviously this is just one value in the column, which is named "Date" (column). I need to convert '01/25/2015' to a date so that it can be queried as an actual date not a string.

My SQLVIEW is the following:

SELECT CDate(Mid([NewDate],4,2)+"/"+Right([NewDate],4)+"/"+Left([NewDate],2)) AS ConvDate, [Reminder Creation Report].EnterUserId
FROM [Reminder Creation Report];

The query returns something like the following:

ConvDate	EnterUserId
#Error	User1
#Error	User1
#Error	User5
#Error	User3
#Error	User2
#Error	User5
#Error	User2
#Error	User4

[June7]

CDate() should be sufficient to convert string into a date value. See posts 9 and 10.