**RESOLVED**SQL Assistance PLEASE!

**Salvatore** · May 19th, 2003, 11:59 AM

The SQL statement below is what I currently have that is working:

VB Code:

rs.Open "SELECT RepName FROM Main WHERE RepName = '" & txtRepName.Text & "'"

I need to ammend this statement with another condition...I also need to query by date that is between #5/1/03# and #5/31/03#.

Something like:

VB Code:

rs.Open "SELECT RepName FROM Main WHERE DateRef BETWEEN #5/1/03# AND #5/31/03#"

My problem is , how do I combine both conditions into one statement?

Thank you.

**mmiill** · May 19th, 2003, 12:14 PM

what's wrong whit this

SELECT * FROM * WHERE *='' AND * BETWEEN
#/1/1/1# AND #1/1/1#

**Salvatore** · May 19th, 2003, 12:33 PM

I need the query to be based on a specific name located in the txtRepname textbox as well as to pull only those records that are within certain dates!

The SQL statement that you provided would give me ALL records that were between certain dates.

**Briantcva** · May 19th, 2003, 12:38 PM

You can have multiple conditions in a WHERE clause.

SELECT ... WHERE RepName='name' AND Date BETWEEN ...

**Salvatore** · May 19th, 2003, 12:58 PM

BriantCVA:

I added the AND keyword, and now I get a Compile Error on the keyword BETWEEN...

VB Code:

rs.Open "SELECT RepName FROM Main WHERE _
 RepName = '" & txtRepName.Text & "'" AND DateRef [B]BETWEEN[/B] #5/1/03# AND #5/31/03#"

I must be missing something....

**apps_tech** · May 19th, 2003, 01:10 PM

visual basic code:--------------------------------------------------------------------------------rs.Open "SELECT RepName FROM Main WHERE _
RepName = '" & txtRepName.Text & "'" AND DateRef BETWEEN #5/1/03# AND #5/31/03#"--------------------------------------------------------------------------------

Your code should be something like this:

rs.Open "SELECT RepName FROM Main WHERE _
RepName = '" & txtRepName.Text & "' AND DateRef BETWEEN #5/1/03# AND #5/31/03#"

Hope this works.

Cheers,
Aparna

**Salvatore** · May 19th, 2003, 01:54 PM

I do not see the difference between the code you provided and the one I currently have....

Please explain the difference.

Thank you.

**apps_tech** · May 19th, 2003, 01:57 PM

In your earlier code, you wrote:
txtRepName.Text & "'" AND DateRef BETWEEN #5/1/03# AND #5/31/03#"

after txtrepname.text u have an &. After that &, you have to open a double quotes, a single quote thats all and the rest of thetext. no need to have the second double quote after the single quote...

I hope u got it..

Thanks,
Aparna

**Memnoch1207** · May 19th, 2003, 02:12 PM

what's db are you using?

**Salvatore** · May 19th, 2003, 02:14 PM

Ok I see what you mean...and it did correct the error, but I am still not getting any results....

This is what I have now:

VB Code:

Dim i as Integer
Dim rs as Recordset
Set rs = New Recordset
 
rs.Open "SELECT RepName FROM Main WHERE _ 
RepName = '" & txtRepName.Text & "' AND DateRef BETWEEN #5/1/03# AND #5/31/03#" 
i=rs.RecordCount
txtRefCount.Text = i
rs.Close

Any ideas????

**Salvatore** · May 19th, 2003, 02:16 PM

Memnoch1207,

I'm using Access as the database.

Is that what you were asking?

**radum** · May 19th, 2003, 02:18 PM

dont break a VB line in the middle of a string...

VB Code:

thisworks = "This is some text " & _
  "and some more text"
thisdoesntwork = "This is some text _
and some more text"

use this:

VB Code:

rs.Open "SELECT RepName FROM Main WHERE " & _ 
  "RepName = '" & txtRepName.Text & "' AND DateRef BETWEEN #5/1/03# AND #5/31/03#"

**Salvatore** · May 19th, 2003, 02:31 PM

i did the break in this forum for the purpose of readability...

In my project it is done correctly, but yet still not wrking properly!!

**Briantcva** · May 19th, 2003, 02:32 PM

post code

**Salvatore** · May 19th, 2003, 02:41 PM

BriantCVA.

I posted the code earlier, but here it is again:

vb

VB Code:

Dim i as Integer
Dim rs as Recordset
Set rs = New Recordset
 
rs.Open "SELECT RepName FROM Main WHERE _ 
RepName = '" & txtRepName.Text & "' AND DateRef BETWEEN #5/1/03# AND #5/31/03#" 
i=rs.RecordCount
txtRefCount.Text = i
rs.Close

**Briantcva** · May 19th, 2003, 03:03 PM

I don't see you implementing any of the above suggestions.
Does this work?

Code:

rs.Open "SELECT RepName FROM Main WHERE RepName = '" & txtRepName.Text & "'"

What about this?

Code:

rs.Open "SELECT RepName FROM Main WHERE RepName = '" & txtRepName.Text & "' AND DateRef BETWEEN #5/1/03# AND #5/31/03#"

**Salvatore** · May 19th, 2003, 03:10 PM

Allright, I have implemented each suggestion, the follwoing code is exactly what I have in my VB project:

VB Code:

Dim i as Integer
Dim rs as Recordset
Set rs = New Recordset
 
rs.Open "SELECT RepName FROM Main WHERE RepName = '" & txtRepName.Text & "' AND DateRef BETWEEN #5/1/03# AND #5/31/03#", MyDataEnvironment.MyConnection 
i=rs.RecordCount
txtRefCount.Text = i
rs.Close

I adjusted the double quote after the txtRepName to a single quote with (1) double quote...this was in accord to the suggestion made by apps_tech.

Besides that suggestion, I have not received any others..

I can not understand why it is not pulling the data...because if I seperate the conditions into (2) SQL SELECT statements, then I will receive accurate results, but when I combine it using the AND keyword, then I get nothing!

**apps_tech** · May 19th, 2003, 03:26 PM

did u try to execute ur sql statment in Access and see whether u are getting any results or not?

**Briantcva** · May 19th, 2003, 03:31 PM

Does the query work (substututing a real RepName for the textbox value) in the query analyzer? That is, is the problem w/ your query or your vb?

**radum** · May 19th, 2003, 03:36 PM

oh boy... make a .MoveLast on the recordset and *then* try to read the .RecordCount property... that property doesnt get set as long as you stay on top.

VB Code:

Dim i as Integer
Dim rs as Recordset
Set rs = New Recordset
 
rs.Open "SELECT RepName FROM Main WHERE RepName = '" & txtRepName.Text & "' AND DateRef BETWEEN #5/1/03# AND #5/31/03#", MyDataEnvironment.MyConnection 
rs.MoveLast ' <-- this is what i added
i=rs.RecordCount
txtRefCount.Text = i
rs.Close

your recordset is actually full, but the .RecordCount is not set.

**apps_tech** · May 19th, 2003, 03:39 PM

good catch radum.....that might help a bit in getting back ur results..

cheers,
aparna

**Salvatore** · May 20th, 2003, 07:26 AM

Randum:

I took your suggestion which by the way made perfect sense, but I am receiving a Run-time error ' 3021 - "Either BOF or EOF is true, or the current record has been deleted; the operation requested by the application requires a current record."

Any ideas as to why this message? I have records in the table.

**Salvatore** · May 20th, 2003, 08:52 AM

I checked to see if the sql statement would work when I subsituted the txtRepName.Text with an actual name of a rep from the table.....

and the sql worked, but when I place the txtRepName.Text back, then I get no value!

What does this mean, something wrong with the reference to the textbox value?

**radum** · May 20th, 2003, 09:24 AM

... no idea... other than i would suggest to double-check the spelling of the text box's name... what is the name that you substituted in the sql statement? was it numeric?

**Briantcva** · May 20th, 2003, 09:24 AM

Could be.

If you assign your SQL string to a variable and then run a debug.print statement, does it look correct? I.e., does it look the same as if you manually entered the repname in? Also, does the repname have an apostrophe in it?

**Salvatore** · May 20th, 2003, 09:31 AM

No the Rep name does not have an apostraphe, and it is not numeric....

It really does not make any sense!!

It just does not want to read both conditions, and if it does it will either not read the contents of the textbox variable and give 0 value!

Something is wrong with this SQL statement, even though it looks fine!

Please guys do not give up on me...I will try the debug print suggestion.

**radum** · May 20th, 2003, 09:42 AM

you have a few guys here (me included) that scratched their heads and came with the most stupid ideas on how to fix it, just because all of them see the sql statement as being perfect. do me a favour - can you please replace the '#' signs around the dates with single quotes? i really do hope youre querying an access database, and not a ms sql or oracle. also, take off that 'BETWEEN' that i really hate and break it into 2 conditions:

VB Code:

SELECT RepName FROM Main WHERE RepName = '" & txtRepName.Text & "' AND DateRef > '5/1/03' AND DateRef < '5/31/03'"

**Briantcva** · May 20th, 2003, 09:53 AM

Access needs dates in #s so that really shouldn't work - and if it does I'm going to shoot myself.

If radums suggestion doesn't work try keeping the double date conditions and swap the single quotes for #s and check that.

**Salvatore** · May 20th, 2003, 10:20 AM

As you guys were scratching your head and providing some new suggestions, I revisited the SQL and thought that maybe the textbox was not giving the SQL the correct data...so I changed the code to read from a textbox on the main form...

AND NOW IT WORKS WONDERFULLY!!!

Thank you both Briantcva & randum!!

You guys are excellent, keep up the great work

**jhermiz** · May 20th, 2003, 10:22 AM

VB Code:

Dim strSQL1 As String
Dim strSQL2 As String
 
strSQL1 = "SELECT RepName FROM Main WHERE RepName = '" & txtRepName.Text & "'"
 
strSQL2 = strSQL1 & " AND DateRef BETWEEN #'5/1/03'# AND #'5/31/03'#"

Jon

Thread: RESOLVEDSQL Assistance PLEASE!

Thread Tools

Display