asked on

HELP with SQL Statement - Please!

I'm trying to write an SQL statement that searches one field for a match but then checks it against a date range in another field but I'm not sure exactly how to write it.

Table Name = CustomerMaster
FieldName1 = SocSecNo - Primary Search Field
FieldName2 = LastActiveDate - Date/Time Field

Pseudo Code.....
SELECT SocSecNo FROM CustomerMaster WHERE SocSecNo LIKE '" & txtSocNo.Text & "' AND LastActiveDate >= {Todays Date + 5}"

I need this so I can search for a customer with a matching social# yet only make a match if they have been in within the last 5 days

ASKER CERTIFIED SOLUTION

jpontani

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Jeff Certain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jpontani

Sorry, it should be

now() - 5

- Joe

Jeff Certain

I see Joe got in before me... His code will work for .Net (where mine won't work), but not in a stored procedure on SQL server (where mine will work).

Are you storing the SSN as an integer or a string?

Jeff

Matrix1000

ASKER

Thanks! I'm storing it as a String

Jeff Certain

As a completely unrelated topic, have you considered storing as an integer? It will save you storage space, and should be more efficient to search.

What database are you using on the back end?

Joe... will Between Now and Now-5 work, or does it have to be "Between Now-5 and Now"?

Jeff

Matrix1000

ASKER

Thanks for the tip. I'm using Access.

jpontani

SELECT [Branch Licenses].[Issue Date] FROM [Branch Licenses] WHERE [Branch Licenses].[Issue Date] Between Now() And Now()-30;

That's one I just did on one of my databases and it worked as I had hoped in Access, so I guess the answer would be no, you don't have to have the values in the query. Access recognizes the Now() function in the SQL.

Jeff Certain

Joe,
I was just curious if it would recognize the BETWEEN High AND Low, instead of BETWEEN Low and High...
Jeff

jpontani

Oh, right, I knew that.....

SELECT [Branch Licenses].[Issue Date] FROM [Branch Licenses] WHERE [Branch Licenses].[Issue Date] Between Now()-30 And Now();

Returns the same results as

SELECT [Branch Licenses].[Issue Date] FROM [Branch Licenses] WHERE [Branch Licenses].[Issue Date] Between Now() And Now()-30;

So the answer is yes, Access recognizes both High/Low and Low/High.

- Joe