Lookup Question

[sseniour]

You are using spaces in field names and no square brackets.


Please read any replies.

I am using spaces in field names, but have those enclosed in brackets. Am I not supposed to do that?

[Welshgasman]

I am using spaces in field names, but have those enclosed in brackets. Am I not supposed to do that?

OK, was looking on my phone.
Yes, you must have [] if you have spaces in object names. Not recommended. I use CamelCase. Some people use _ in place of the space.

Show us the result of the Q_First.... and the SQL please.
I would not prefix the fieldnames with the domain name, you are already supplying that. Try and keep things as simple as possible.

Alternative, upload enough of the DB to see the issue. I myself work best when having the code to hand and test.

[sseniour]

I have revamped my db as some have suggested to not include spaces in field names. My Production_Minutes table has "Time" (Date/Time field formatted as Short TIme. Also has field "ProdMins".




My dlookup formula is returning blanks. I'm sure it has somthing to do with formatting but just not sure what. The "CurrFUlTime" is a text field.

many suggested to get my infomation from a formula instead of using DLookup. Will dlookup not work? Seems easier than writing formula with all the if statements? Appreicate all the feedback

[Welshgasman]

Your Dlookup is wrong. Date and times must be surrounded with #
Try

CurrProdMins: DLookUp("ProdMins","Production_Minutes","Time = #" & CurrFUITime & "#")

CurrFUITime should not be text, but a time datatype, else use TIMEVALUE() as I have mentioned several times.
If it was text like a name then you surround with single quotes or triple double quotes (I think) if that could contain a single quote like O'Neil

[mike60smart]

Can you upload a copy of the database?

[June7]

Date/Time type field always has a date component even if field is formatted to ShortTime. Format property does not change data. Format function does.
You say Time field is date/time type yet CurrFUITime is text type. Have to convert Time to text or CurrFUITime to date/time. Both worked for me.

DLookUp("ProdMins","Production_Minutes","Format(Time,'hh:nn')='" & [CurrFUITime] & "'")

or

DLookUp("ProdMins","Production_Minutes","Time=#" & [CurrFUITime] & "#")

[Welshgasman]

Date/Time type field always has a date component even if field is formatted to ShortTime.

I thought that, but try putting just a time into a formatted short time field, then change it to general, then look at it.

? dlookup("inputTime","Table1","ID=1")
17:36:00 

? format(dlookup("inputTime","Table1","ID=1"),"dd/mm/yy hh:mm:00")
30/12/99 17:36:00

[CJ_London]

Seems easier than writing formula

22 posts and counting.

I’m struggling to understand a production line that has a start time of 6:36 every day, with breaks at exactly the same time and length every day, never has a breakdown or stoppage for some reason. Unless it really never changes you need to recreate your lookup table all the time.

but simply logging that detail in a table a formula along the lines I suggested would be simple to implement