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
LVL 1
Matrix1000Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jpontaniCommented:
SELECT SocSecNo FROM CustomerMaster WHERE SocSecNo LIKE '" & txtSocNo.Text & "' AND LastActiveDate BETWEEN '" & now() & "' AND '" & now() + 5

Also, you use the LIKE syntax with no % to match for other cases.  You might as well just replace it with WHERE SocSecNo='" & txtSocNo.Text

- Joe
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeff CertainCommented:
The code you're looking for is
"SELECT SocSecNo FROM CustomerMaster WHERE SocSecNo=" & Integer.Parse(txtSocNo.Text.ToString) & " AND DATEDIFF (d,LastActiveDate, GetDate())<=5"

Jeff
0
jpontaniCommented:
Sorry, it should be

now() - 5

- Joe
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Jeff CertainCommented:
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
0
Matrix1000Author Commented:
Thanks! I'm storing it as a String
0
Jeff CertainCommented:
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
0
Matrix1000Author Commented:
Thanks for the tip. I'm using Access.
0
jpontaniCommented:
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.
0
Jeff CertainCommented:
Joe,
I was just curious if it would recognize the BETWEEN High AND Low, instead of BETWEEN Low and High...
Jeff
0
jpontaniCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.