Help please with sql in VB

[neh326]

Hi, just joined hoping for help. I've arranged the code below a zillion different ways. I had it running without error once but it pulled all the data rather than the data specified in the date criteria. I viewed the msgbox and don't see a problem with the sql but obviously there is a problem. I'm getting an error now on the where clause. I'm out of ideas and will greatly appreciate your help!

intSrtMonth = 4
intEndMonth = 6
intEndDay = 30
intYear = 2011

strSQL = "INSERT INTO [Qtrly Undup Report 2] "
strSQL = strSQL & "SELECT DISTINCT [Qtrly Dup Report 1].[PT_ID], [Qtrly Dup Report 1].[PT_SSN], [Qtrly Dup Report 1].[PT_CLTSPT_ID], "
strSQL = strSQL & "[Qtrly Dup Report 1].[PT_RACE_CD], [Qtrly Dup Report 1].[PT_SEX], [Qtrly Dup Report 1].[PT_BIRTH_DT], [Qtrly Dup Report 1].[CF_SVC_DT] "
strSQL = strSQL & "FROM [Qtrly Dup Report 1] "
strSQL = strSQL & "Where ([Qtrly Dup Report 1].[CF_SVC_DT] between #"
strSQL = strSQL & intSrtMonth & "/" & 1 & "/" & "/" & intYear
strSQL = strSQL & "# and #"
strSQL = strSQL & intEndMonth & "/" & intEndDay & "/" & "/" & intYear & "#)"

DoCmd.RunSQL (strSQL)

 

[the_net_2.0]

wow.

well let's see...you're concating numeric variables and combining them to make a date, and enclosing that date in # signs. I would assume the error would be one of two things:

1) you haven't cast the different date concatenations to an actual DATE value - use CDATE()

2) access doesn't like the fact that you're concating numeric variables with strings like "/". that never works, regardless of scenario.

 

[spikepl]

Insert

debug.print strSQL

just before your DoCmd.RunSQL

and inspect the result in the Immediate window. You cannot just insert some number of "/"-separators randomly here and there and everywhere.

If still in doubt, copy that SQL string from the Immediate window, and insert it into the SQL-View of the query designer, and see what it has to say.

 

[Galaxiom]

I would assume the error would be one of two things:

1) you haven't cast the different date concatenations to an actual DATE value - use CDATE()

2) access doesn't like the fact that you're concating numeric variables with strings like "/". that never works, regardless of scenario.

Sorry the_net_2.0 but you are completely mistaken on both counts. SQL commands are built as strings.

 

[Galaxiom]

The problem is that you have concatenated two slashes into the dates between the day and year.

I would also advise you to change the layout the construction of the string.

Abandon the clumsy iterative concatenation which repeats the variable over and over obscuring the code. Use continuation ( space underscore)which works up to 22 continuations. If you need more than that build subclauses with continuation then concatenate them.

Move the spaces to the front of the subsequent lines where they are far more easily seen because of the alignment.

Leave out the unnecessary repetition of the tablename. It is not required if there is no ambiguity.

Don't unnecessarily concatenate.
& "/" & 1 & "/" &
is the same as
& "/1/" &

strSQL = "INSERT INTO [Qtrly Undup Report 2]" _
       & " SELECT DISTINCT PT_ID, PT_SSN, PT_CLTSPT_ID," _
       & " PT_RACE_CD, PT_SEX, PT_BIRTH_DT, CF_SVC_DT" _
       & " FROM [Qtrly Dup Report 1]" _
       & " WHERE CF_SVC_DT" _
       & " BETWEEN #" & intSrtMonth & "/1/" & intYear & "#" _
       & " AND #" & intEndMonth & "/" & intEndDay & "/" & intYear & "#"

 

[vbaInet]

I would use DateSerial(), Format() or DateValue to ensure that it formats the date properly. DateSerial() example:

strSQL = "INSERT INTO [Qtrly Undup Report 2]" _
       & " SELECT DISTINCT PT_ID, PT_SSN, PT_CLTSPT_ID," _
       & " PT_RACE_CD, PT_SEX, PT_BIRTH_DT, CF_SVC_DT" _
       & " FROM [Qtrly Dup Report 1]" _
       & " WHERE CF_SVC_DT" _
       & " BETWEEN #" & [COLOR=Blue]DateSerial([/COLOR]intYear, intSrtMonth, 1[COLOR=Blue]) [/COLOR]& "#" _
       & " AND #" & [COLOR=Blue]DateSerial([/COLOR]intYear, intEndMonth, intEndDay[COLOR=Blue])[/COLOR] & "#"

With that you may not even need the hash (#), but that's for you to test.

 

[Galaxiom]

I would use DateSerial(), Format() or DateValue to ensure that it formats the date properly.

DateSerial isn't going to do the job (except in the US) because it will return the date in the regional format. Remember we are building an SQL query string and the date should be formatted #mm/dd/yyyy#.

One could use:

Format(DateSerial(intYear, intEndMonth, intEndDay),"\#mm\/dd\/yyyy\#")

 

[vbaInet]

Good call GalaxiomAtHome.

 

[neh326]

Galaxiom: This worked the first time! Thank you so much... you have made my day.

Also, thank you to everyone who took a stab at my problem.

The problem is that you have concatenated two slashes into the dates between the day and year.

I would also advise you to change the layout the construction of the string.

Abandon the clumsy iterative concatenation which repeats the variable over and over obscuring the code. Use continuation ( space underscore)which works up to 22 continuations. If you need more than that build subclauses with continuation then concatenate them.

Move the spaces to the front of the subsequent lines where they are far more easily seen because of the alignment.

Leave out the unnecessary repetition of the tablename. It is not required if there is no ambiguity.

Don't unnecessarily concatenate.
& "/" & 1 & "/" &
is the same as
& "/1/" &

strSQL = "INSERT INTO [Qtrly Undup Report 2]" _
       & " SELECT DISTINCT PT_ID, PT_SSN, PT_CLTSPT_ID," _
       & " PT_RACE_CD, PT_SEX, PT_BIRTH_DT, CF_SVC_DT" _
       & " FROM [Qtrly Dup Report 1]" _
       & " WHERE CF_SVC_DT" _
       & " BETWEEN #" & intSrtMonth & "/1/" & intYear & "#" _
       & " AND #" & intEndMonth & "/" & intEndDay & "/" & intYear & "#"

 

[the_net_2.0]

SQL commands are built as strings.

I might believe you there sir, but not your first one. I'd have to check it and make sure you're actually right about it.

My post was about guesses too, not real facts. Pretty much like always? Oh hey, don't tell Bob about that!

 

[vbaInet]

I think the main point he was trying to get across is that the date was built before being passed to the engine, so the concatenation is valid. Also, the hash attempts to format it as a date fit for the engine.

I will try not to tell Bob, but hey who knows. Bob might perform a search for his name and see this thread lol

 

[the_net_2.0]

I will try not to tell Bob, but hey who knows. Bob might perform a search for his name and see this thread lol

to this day, I still don't know how the admins monitor posts, because there are so many of them. I have a sneaking suspicion that they still do it manually, but vbulletin could have built in alerts that are sent when new posts come up. Not sure.

but anyway, thanks for not telling sir!

 

[vbaInet]

I have a sneaking suspicion that they still do it manually, but vbulletin could have built in alerts that are sent when new posts come up.

That would be ridiculous if they do it manually. I'm sure vbulletin has some extra functionality. Plus there are more Mods now than there was a few years ago. You should know better about the number of Mods in the past than myself