Expression issues

[doomy304]

Hello,

I have two tables. One is Agents, with fields AgentName (text) and ExpirationDate (date/time). The other table is Hours, with fields AgentName (linked to Agents), HoursDate (date/time), and HoursNumber (number).

I have a data entry form for the Hours table, and I have a Save Record button on it, and when the user clicks the Save Record button, I want it to check to see whether the HoursDate is before or after the ExpirationDate linked to that same agent and take action accordingly.

I'm using a macro to do this but I am open to VB if that would be easier.

I tried If Agents![ExpirationDate] > [HoursDate] Then blahblah but that popped up syntax error. Then I tried If [HoursDate]-Agents![ExpirationDate] > 0 Then blahblah but that also gave me syntax error.



I am sure I am missing something obvious here. Please help!

[doomy304]

The "blahblah" is just message boxes, by the way, so I'm pretty sure the syntax error is not coming from that.

[pbaldy]

You can't refer to an unrelated table that way (a table not included in the record source of the form). You can use DLookup() or open a recordset to get the value. Here's the DLookup syntax:

DLookup Usage Samples

[doomy304]

Thanks, that really helps! But I am still getting the invalid syntax error.

Here is what I have now:

If DLookup("[ExpirationDate]","Agents","[AgentName] = Forms![FormName]![AgentName]") > [HoursDate] Then messagebox

I am pretty sure at this point it's a brackets/quotes issue, right?

[doomy304]

OK, fixed one issue - had spaces in my form name, renamed the form to be spacefree and now I am getting error Data type mismatch in criteria expression which is an improvement I think!

edit: FIGURED IT OUT!!! The problem was that due to the way I had my lookups set up, [AgentName] in the Agents table was a text field, and [AgentName] in the form was a number. So I switched it to

DLookUp("[ExpirationDate]","Agents","[ID] = " & [Forms]![FormName]![AgentName])>[HoursDate]

and now it is doing exactly what I want YESSSS

[pbaldy]

Glad you got it sorted out.