If datetime filed is null the insert created date

[Killiman]

Hello all.

I am trying to figure out how to create a date and time, where the field is null.

I am pulling records that are 24 hours from a user picked date and hour where the user enters a date ([Report Date]="5-10-17") and hour ([Hour]="5" .

Statement:
WHERE (((DateAdd("h",0-[Hour],[detailStartDateTime]))<DateAdd("h",24,DateValue([Report Date]))) AND ((IIf(Not IsNull([detailStopDateTime]),DateAdd("h",0-[Hour],[detailStopDateTime])))>=DateAdd("h",0,DateValue([Report Date]))))

Does anyone know how I can create this date and time in the correct format from the user input?

Thank you

 

[plog]

Can you better explain what you are trying to achieve? Specifically, answer these questions:

1. Whats the name of the field(s) in the underlying table that you will be comparing the user input to?

2. Do you really mean all records within 24 hours of the user supplied date and time? Because that makes a 48 hour window. If the user supplies 5/11/2017 and 1pm then records occuring from 5/10/2017 1:00 pm to 5/12/2017 1:00 PM are all within 24 hours of the user supplied data.

 

[Killiman]

Sorry this isn't clear. Had many things happening at time of post.

The fields that are being compared to are [detailStartDateTime] and [detailStopDateTime] where if the job hasn't closed at time of report the [detailStopDateTime] is null.

The above statement ignores the null fields because of reasons unknown to me(iif not isNull). I want it to be (iff isNull([detailStopTime]) then make detailStopTime equal [Report Date]+[Hour]), but in a proper datetime format.
I'm not really proficient with sql and am trying to fix this query to include the segments that are being ignored because they are null.

2. The above statement has the <DateAdd("h",24,DateValue([Report Date] so it goes back 24 hours from the user input [hour] value.

I hope I make sense, I am confused too.

 

[Killiman]

What I want to happen is:

if [DateTime Field] is null then make [DateTime Field] = [Report Date]+[Hour], else use [DateTime Field]

 

[plog]

No, that didn't help. I don't really care what your non-working code is. I want to know what you are trying to accomplish. Perhaps you can demonstrate this with data.

Give me sample data from your table (and then any inputs--I'm actually uncertain now if users are providing input or not now). And then show me what you expect the resulting data to be.

 

[moke123]

what about something like

nz([DateTime Field], [Report Date]+[Hour])

 

[Killiman]

Thanks to those who replied.

I went with a simple solution of just using an expression

detailStopDateTime1: IIf(IsNull([detailStopDateTime]),Now(),[detailStopDateTime])

and changing the rest to look at the new column