If Else or Select Case in Access

[jason]

Using Access 2003.
I have a main address table and alternate address table for our customers.
In the alternate address table, we have a date of when the customer will be
at that location.
What I'm trying to do is check whether today falls between the alternate
address' date span and if it is, show it in a query. If today's date does
NOT fall between the alternate address dates, I just want to show the
address info from the main address table.

I thought that I could do something like:

DIM altAddress as String

altAddress = select altStreet, altCity, AltState, altETC.... FROM
altAddressTbl
WHERE today() >= altBeginDate AND today() <=altEndDate


I know that's not the full code above, but I can't get much further than
that because when I try to run this, I get an error saying that it expects a
DELETE, INSERT, PROCEDURE, SELECT, OR UPDATE and won't let me continue.

Is there something I'm missing here?

Thanks.

 

[Bill Mosca, MS Access MVP]

Jason

A string must be surrounded by double quotes. Dates need octothorpes (#)
around them.

And Access VBA and SQL use Date() for the current date; Excel uses Today()

You code should look more like this:
DIM altAddress as String

altAddress = "select altStreet, altCity, AltState, altETC...." _
& " FROM " & altAddressTbl
& " WHERE Date() >=#" & altBeginDate _
& "# AND Date() <=#" & altEndDate & "#"

 

[kingston via AccessMonster.com]

Is there a reason why there needs to be two tables for address information?
Would it work for you if you combined the data in one table and added a field
that indicated primary or alternate? I think this would help you sort this
out. Otherwise, you'll need some type of key that is the same in both tables
for a customer. Then you'd include both tables in the query and create a
join on that key. For the fields, you'd use the function IIF() to check the
date and output data from one table or the other.

IIF(Date Between [AltTbl].[BeginDate] And [AltTbl].[EndDate],[AltTbl].[Field1]
,[MainTbl].[Field1])

 

[jason]

Thanks for the reply, Bill.
After using your ideas to create the code, I keep getting the error:
Invalid SQL statement; expected DELETE, INSERT, PROCEDURE, SELECT, OR
UPDATE.

 

[jason]

It would be easier in one table, yes. But the data I'm working with is being
exported from a different program.

Is there a reason why there needs to be two tables for address
information?
Would it work for you if you combined the data in one table and added a
field
that indicated primary or alternate? I think this would help you sort
this
out. Otherwise, you'll need some type of key that is the same in both
tables
for a customer. Then you'd include both tables in the query and create a
join on that key. For the fields, you'd use the function IIF() to check
the
date and output data from one table or the other.

IIF(Date Between [AltTbl].[BeginDate] And
[AltTbl].[EndDate],[AltTbl].[Field1]
,[MainTbl].[Field1])

Using Access 2003.
I have a main address table and alternate address table for our customers.
In the alternate address table, we have a date of when the customer will
be
at that location.
What I'm trying to do is check whether today falls between the alternate
address' date span and if it is, show it in a query. If today's date does
NOT fall between the alternate address dates, I just want to show the
address info from the main address table.

I thought that I could do something like:

DIM altAddress as String

altAddress = select altStreet, altCity, AltState, altETC.... FROM
altAddressTbl
WHERE today() >= altBeginDate AND today() <=altEndDate

I know that's not the full code above, but I can't get much further than
that because when I try to run this, I get an error saying that it expects
a
DELETE, INSERT, PROCEDURE, SELECT, OR UPDATE and won't let me continue.

Is there something I'm missing here?

Thanks.

 

[Bill Mosca, MS Access MVP]

Jason

Debugging SQL statements is a bit tricky.

Put a break point on the line after:
altAddress = "select altStreet, altCity, AltState, altETC...." _
& " FROM " & altAddressTbl
& " WHERE Date() >=#" & altBeginDate _
& "# AND Date() <=#" & altEndDate & "#"


and run the code so the code stops at that point. Then open the immediate
window by pressing Ctrl+G. Type ?altAddress in the Immediate window and
press Enter. This will give you a display of the SQL string. Copy that
string and paste it into the query SQL window and run it. You will probably
get a syntax error message that will help you figure out what is wrong.

If you can't figure it out, paste that line here so we can see it.