?
Solved

HELP with SQL Statement - Please!

Posted on 2004-11-16
10
Medium Priority
?
246 Views
Last Modified: 2010-04-24
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
0
Comment
Question by:Matrix1000
  • 4
  • 4
  • 2
10 Comments
 
LVL 5

Accepted Solution

by:
jpontani earned 1200 total points
ID: 12596049
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
 
LVL 24

Assisted Solution

by:Jeff Certain
Jeff Certain earned 800 total points
ID: 12596053
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
 
LVL 5

Expert Comment

by:jpontani
ID: 12596057
Sorry, it should be

now() - 5

- Joe
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 24

Expert Comment

by:Jeff Certain
ID: 12596070
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
 
LVL 1

Author Comment

by:Matrix1000
ID: 12596281
Thanks! I'm storing it as a String
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 12596346
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
 
LVL 1

Author Comment

by:Matrix1000
ID: 12596373
Thanks for the tip. I'm using Access.
0
 
LVL 5

Expert Comment

by:jpontani
ID: 12597208
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
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 12597237
Joe,
I was just curious if it would recognize the BETWEEN High AND Low, instead of BETWEEN Low and High...
Jeff
0
 
LVL 5

Expert Comment

by:jpontani
ID: 12597269
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Screencast - Getting to Know the Pipeline
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question