CurrentDateTime problem 1

[JetRamsey]

I would like to use the formula of (currentdatetime - 7), which is what I use for many reports. Unfortunately, the new database that I'm reading records from has the output for a date field as "2013/10/08 00:00:00". So, when I use the Currentdatetime - 7, it doesn't like the date format and errors out with a "A date-time is required here.". Is there away around this problem?

TIA

 

[wanzek1]

Try using just (CurrentDate - 7)

 

[SkipVought]

hi,

datetime - 7

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[pmax9999]

Are you saying the Date/Time in the database is actually being stored as text?

In which case, you would need to either convert the database field to Date/Time (or Date) format, or the formula result to a String.

If this is being used in your Record Selection formula, you could do it like this:

Date({Table.DateTimeStringField}) = (CurrentDate-7)

Hope this helps

Pete

 

[JetRamsey]

Yes, it's stored as text. Unfortunately, doing it as Date({Table.DateTimeStringField}) = (CurrentDate-7) takes several minutes to run, which is probably because the report has to convert every text to address?. However, when I use:

{IMAGES_RECOGNHOT_VIEW.EVENTDT} > "2013/10/2 11:31:10"

the report runs in half a second.

Now, I just need to figure out a formula to be (currentdatetime - 7) and have it appear as "2013/10/2 11:31:10" That's what I'm having a challenge with right now.

 

[kray4660]

I was having a performance issue doing something similar. You might convert the currentdatetime - 7 to text and do the comparison. It might work out for you.

 

[JetRamsey]

Thanks for the input Kray. Unfortunately, query takes too long and even gave back the wrong results. Weird.

 

[pmax9999]

It isn't clear from your posts the exact format of the date/time string, ie whether the date is:
[ul]
[li]yyyy/MM/dd[/li]
[li]yyyy/M/dd[/li]
[li]yyyy/MM/d[/li]
[li]yyyy/M/d[/li]
[/ul]

You may therefore need to amend the format component, but it will be something like this:

ToText(CurrentDateTime - 7, "yyyy/M/d HH:mm:ss")

Cheers
Pete

 

[JetRamsey]

Pmax. That did it. Thx