Problem with Dlookup statement

[jgarnick]

Why won't the dlookup statement below work? I keep getting an invalid use of null, yet the value is in the table. Is my syntax wrong? I tried troubleshooting using other fields in the table and they worked. App_period is a text box. Any ideas? Thanks-

Dim rs As Recordset
Dim strinput As String
Dim strmsg As String
Dim varx As Variant
Set db = CurrentDb

Set rs = db.OpenRecordset("variables&quot

strmsg = "Enter a NEW application period."
strinput = inputbox(prompt:=strmsg, TITLE:="NEW APPLICATION PERIOD&quot

varx = DLookup("[app_period]", "variables", "[app_period] =" & strinput)

I'm trying to avoid duplicate entry of the same value into the table. [sig]<p>jgarnick<br><a href=mailto:jgarnick@aol.com>jgarnick@aol.com</a><br><a href= > </a><br> [/sig]

 

[cmmrfrds]

A text field needs to be enclosed in quotes. If the field is truely unique, then put &quot;no duplicates&quot; in the table properties for this field.
Dim dq as String
dq = Chr$(34) '- this is double qoutes

&quot;[app_period] =&quot; & dq & strinput & dq)

Jerry [sig][/sig]

 

[WP]

You don't require double quotes single quote will do and could just have

varx = DLookup(&quot;[app_period]&quot;, &quot;variables&quot;, &quot;[app_period] ='&quot; & strinput & &quot;'&quot

But why do you define db and recordset objects and then not use them.

You could do:

Dim strSQL as Strng

strSLQ = &quot;SELECT app_period FROM variables WHERE app_period='&quot; & strinput & &quot;';&quot;

Set rs=Currentdb.openrecordset(strSQL)

Then you could have :

If rs.RecordCount <>0
Msgbox &quot;This application period already exists&quot;
Else
Currentdb.Execute &quot;INSERT INTO variables (app_period) Values('&quot; & strinput & &quot;');&quot;
EndIf

rs.close

set rs=nothing


WP [sig]<p>Bill Paton<br><a href=mailto:william.paton@ubsw.com>william.paton@ubsw.com</a><br><a href=

http://www.neptune400.co.uk>www.neptune400.co.uk</a><br>

[/sig]

 

[petermeachem]

Actually, single quotes are fine unless you have a single quote in the string. E.g. look for Mc'Petrol to be topical. The chr$(34) is much safer, but more of a pain to use. [sig][/sig]