How to select part of the field for query lookups 1

[Stevehewitt]

Hi Guys,

I have a linked table from a third party database where the date / time field is presented like so:

"2005-03-01 17:18:57 by Steven Hewitt"

I want to run queries on the table based on the date only. How would I go about doing this? Is it something easy or would I need to look at using VBA?

Thanks,


Steve

 

[Stevehewitt]

Sorry, let me rephrase that.

I am using the "Like" operator (E.G. Like "2005-03-01*") but that only works when it's in the query as a static string. I need to be able to enter that into the query as a parameter, but it doesn't seem to like it!

Thanks.


Steve.

 

[SkipVought]

Steve,

1. realize that Date/Time values in MS Systems are NUMBERS.

Time values are in fractions of a day.

2. However, this value is a STRING. Therefore, parse the string into year, month and day and CONVERT to a DateSerial value

s = "2005-03-01 17:18:57 by Steven Hewitt"
TheDate = dateserial(left(s,4), mid(s,6,2), mid(s,9,2))
[code]
so your sql might look something like this...
[code]
sql = "select * from mytable where dateserial(left([TheField],4), mid([TheField],6,2), mid([TheField],9,2))<date()

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[SkipVought]

what..

sql = "...where [MyField] Like '*" & Format(SomeDate,"yyyy-mm-dd") & "*'..."

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[lespaul]

I think he wants a prompt to enter the date he's looking for:

WHERE [MyField] Like '*" & [What day to search for?] & "*'

Leslie

 

[PHV]

PARAMETERS [Enter the date] DateTime;
SELECT ...
...
WHERE [date / time field] Like Format([Enter the date],'yyy-mm-dd') & ' *';

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Stevehewitt]

Hi Everyone,

Thanks very much for all of your help - really appreciated!

PHV provided the answer for me (although I believe that they would all have worked). It's also helped me understand SQL a bit better!

Thanks again to you all for your time, and a star for PHV.

Cheers,


Steve.

 

[Stevehewitt]

Sorry guys, one more thing related to this. As you know, I have the following expression:

(((TransactionLogTable.LastModified) Like Format([Enter the date],'yyyy-mm-dd') & ' *'));

How would I go about changing it so I can query between two dates?

E.G:

Between [date1] And [Date2]

I've tried hunting in the Access Help but nothing so far.

Cheers,


Steve.

 

[PHV]

Take a look at the Between operator:
PARAMETERS [Enter start date] DateTime, [Enter end date] DateTime;
SELECT ...
...
WHERE TransactionLogTable.LastModified Between Format([Enter start date],'yyyy-mm-dd') And Format([Enter end date],'yyyy-mm-dd') & 'z'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Stevehewitt]

Your a diamond!

Thanks very much - works a charm!


Steve.