Converting a SQL Statement to a Dlookup

[ODLStelco]

Hello all

I have the following SQL Statement which returns the desired result to me

SELECT tbl_p_stats.i_closing
FROM tbl_p_stats
WHERE (((tbl_p_stats.i_weekending)=[forms]![frm_a]![i_olddate]) AND ((tbl_p_stats.i_sacat)=[forms]![frm_a]![wfid1]) AND ((tbl_p_stats.i_complexity)=[forms]![frm_a]![wfc1]));

and am now trying to convert it to a Dlookup to provide the default value to a control on form Loading. The statement i came up with is

= DLookup("[i_closing]", "tbl_p_stats", "[i_weekending] = " & i_olddate & " AND [i_sacat] = " & wfid1 & " AND [i_complexity] = " & wfc1)

However, it is showing the control (wf1oi) as a blank control now. When i run the dlookup through the immediate window, the returned result is Null.

A little more detail
- All the mentioned controls live on the same form (frm_a)
- The control that this Dlookup is going into is called wf1oi

Any thoughts where I have gone wrong here?

Thanks in Advance

 

[rzw0wr]

what is text and what are numbers.

The text needs to be delimited or surrounded with quoues.
Dale

 

[ODLStelco]

Hey Dale

All the control values are numeric

- i_olddate is a date value
- wfid1 and wfc1 are numeric

The result to be placed into wf1oi would be numeric as well

Hope this helps explain it better

 

[rzw0wr]

[FONT=&quot]tbl_p_stats.i_weekending, [/FONT][FONT=&quot][FONT=&quot]tbl_p_stats.i_sacat and [/FONT][/FONT][FONT=&quot][FONT=&quot][FONT=&quot]_p_stats.i_complexity[/FONT][/FONT] needs to be in the Select part of the query.

I THINK. Not real sure on this.

Dale
[/FONT]

 

[ODLStelco]

Hey Dale

Just trying to clarify here.

My SQL Statement works fine, but the DLookup is not. I am trying to have the dlookup provide the same result as the SQL i posted, so that when the form loads, the control wf1oi has the default value set by it. Currently, the dlookup is providing a Null response.

I'm not sure that in Dlookup if I have to refer to the criteria beyond containing them in the criteria arguments at the end of the expression.

Thanks!
Chris

 

[pbaldy]

If the first field has a date/time data type, try

= DLookup("[i_closing]", "tbl_p_stats", "[i_weekending] = #" & i_olddate & "# AND [i_sacat] = " & wfid1 & " AND [i_complexity] = " & wfc1)

 

[rzw0wr]

Sorry, Got all backwards.

Thanks Paul.

Dale

 

[pbaldy]

Sorry, Got all backwards.

No, you were right that text data types need delimiters, just missed that date/time values do too (but a different one). Remains to be seen if that fixes the problem.

 

[rzw0wr]

I was working on query not the Dlookup().
All wrong.
MORE COFFEE IS NEEDED.

Dale

 

[ODLStelco]

Hey Guys

Dale - Not to worry... im a little short on coffee this morning as well

Paul - That fixed it, the delimiters for date were added, and the system is displayinig the data correctly now.

Thanks for the assist!
Chris

 

[pbaldy]

Happy to help. I must have an advantage because I don't drink coffee!