Lookup Question

[sseniour]

Have a query that has short time field. trying to use that field to lookup prod mins in a table, but I can't get syntax correct.

Example: Query field has 12:36. Want to look up 12:36 in the below table and return 303. TIA

[CJ_London]

not enough information to suggest a solution for your problem. Please clarify

1. is 12:36 a datetime field or a text field? - ignore (I can't read!)
2. what is the 'below table'. Also provide some example data from that table
3. provide an example of the syntax you have tried - plus any error messages
4. where are you doing the lookup? in the query? in a form? in a report?

[Welshgasman]

Use TimeValue()

No table below.
You need to use the attach option on this site.

[sseniour]

12:36 is a date/time field in query1. In query2, trying to use that time and "lookup" prod mins in a table that looks like this

Time Prod Mins
12:33:00 300
12:34:00 301
12:35:00 302
12:36:00 303
12:37:00 304
etc...

so ending result would be 303

thanks

[Welshgasman]

Use a join? or as I have already said try Timevalue()
If both are short time then a join should work, else a DlookUp()

[davegri]

If you want to extract just the time from a date/time variable, this will do it.

format(timevalue(now),"hh:nn")
results in 09:49

[sseniour]

This is what I'm using to try and lookup

Current Prod Min: DLookUp("Prod Mins","Production Minutes","New 1st unload Time=#" & "OT 00 Time" & "#")

Table = "Production Minutes"

Time Prod Mins
12:33:00 300
12:34:00 301
12:35:00 302
12:36:00 303
12:37:00 304

New 1st unload Time = 12:36

So result of formula should be 303.

Thanks!

[Welshgasman]

If you want to extract just the time from a date/time variable, this will do it.

format(timevalue(now),"hh:nn")
results in 09:49

That produces a string though?

[Welshgasman]

If you must have spaces in field names (stupid in my opinion, much more hard work) then you need to enclose within []
Put the criteria into in string variable and debug.print it until you get it correct.

? dlookup("PressureID","tblPressure","Timevalue(PressureDateTime)= Timevalue('13:15:07')")
 937

Edit: If that is actually your table, you could just do the math?, but that would still involve a lookup unless you know the starting value is always the same?

[sseniour]

Not sure where to go from here.... the name that is referenced, "Current 1st Unload Time" is a field in the query Q_FirstUnloadTimes3

[CJ_London]

Seems a weird value to assign to 12:33 (I presume PM) 300 implies production started at 7:33AM - why not 7:00AM?

from what you have provided, you could do this with a simple formula

as a time value, subtract 7:03 from your time and format as minutes. On my phone so cannot provide the actual formula at the moment

[sseniour]

time starts at 06:36 and but there are breaks throughout the day and evening. For instance 15:15 = 450 and 19:21 = 451, so not sure a simple formula would work. That is why I set up a table to equate time of day to # of prod mins

[Welshgasman]

You are using spaces in field names and no square brackets.
Please read any replies.

[CJ_London]

time starts at 06:36 and but there are breaks throughout the day and evening. For instance 15:15 = 450 and 19:21 = 451, so not sure a simple formula would work.

So a 4hr+ break in the afternoon?

assuming your date fields are datetime and not text then your formula might look like this (done in the immediate window)
?(#12:33#-(#06:36#+iif(#12:33#>#08:00#,#00:57#,0)+iif(#12:33 #>#15:15#,#04:05#,0)))*24*60
300



or for later in the day
?(#21:05#-(#06:36#+iif(#21:05#>#08:00#,#00:28#,0)+iif(#21:05 #>#15:15#,#04:05#,0)))*24*60
596


add more iifs as required - but probably easier to manage in a function

#12:33# and #21:05# are the times you are trying to get the minute count
#06:36# is your production start time
#08:00# and #15:15# are the start of your breaks
#00:28# and #04:05# are the lengths of the breaks

Are you aware for what a datetime field actually is? it is a double type number, the value before the decimal point is the number of days since 30/12/1899 (which is zero). and the part after the decimal point is the time in seconds divided by 86400, the number of seconds in a day (24*60*60)

So now
?cdbl(now())
45898.0368055556

?cdbl(date())
45898

?cdbl(#12:00#)
0.5

all you are seeing with 12:00 or any other time is a format, not the actual underlying value


So all you are doing is adding and subtracting numbers

[sseniour]

I do have spaces in field names and I have those enclosed in brackets?? Am I not supposed to do that?