Zomg Another date question! (1 Viewer)

[Xproterg]

Zomg Another date question! (PICS Included)

Hey guys,

I've hit a bit of a brick wall, so I'm here asking the guru's of code what to do.

The code below represents three sql queries that are exectuted on button push. One query is for students arriving, they are paid their initial paycheck. Consequently, the code is supposed to recognize which course they're on (of a possible 3 as you can see with the null checks) AND it checks to see if the month clicked (in this case 1/1 or january) is equal to the month they got here. As you can see below, I'm trying to compare months and years only, because this isn't day specific.

Dim janu as string
Dim januyear as string
Dim januinitial as date
Dim strSQL8 as string
Dim strSQL6 as string
 
janu = "1/1/"
januyear = Text130.value
januinitial = janu & Januyear
janumonthly = janu & Januyear
 
strSQL8 = "
 
SELECT Students_Info.firstname, Students_Info.lastname 
FROM Students_Info 
WHERE ((Year(#" & januinitial & "#) = Year(Students_Info.startONE) 
AND Month(#" & januinitial & "#) = Month(Students_Info.startONE));"

This returns nothing... and I have no idea why.

The plot thickens with the next query. I'm trying to use the BETWEEN operator in SQL to get dates between the start dates and grad dates. Unfortunately, it's including the initial month and the final month. I'm not quit sure how to avoid this.

strSQL6 = "SELECT Students_Info.firstname, Students_Info.lastname 
FROM Students_Info 
WHERE ((#" & janumonthly & "# BETWEEN Students_Info.startONE AND Students_Info.gradONE) and Students_Info.gradTWO Is Null) 
 
OR ((#" & janumonthly & "# BETWEEN Students_Info.startONE AND Students_Info.gradTWO) and Students_Info.gradTHR Is Null) 
 
OR (#" & janumonthly & "# BETWEEN Students_Info.startONE AND Students_Info.gradTHR);"

I use null checks in the code above because some students do not have three courses. Anyway, this code is pulling stuff that isn't supposed to be pulled, aka the initial and final months.

Anyways guys, any help would greatly be appreciated. Looking forward to learning from you.

UPDATE: Pictures added so you know what i'm talking about.

 

[pbaldy]

I assume you haven't posted all the code, since you aren't using the strings here. Offhand, you use januinitial in your string but it doesn't appear to have been set to anything (janufinal was).

Between pulls values inclusive of the values used, so if you don't want those you'll need to adjust the values used in the Between clause (like adding 1 to the beginning value and subtracting one from the ending value).

 

[Xproterg]

I've altered the code, and will take your suggestion. Looks like i'll be using the dateadd function. I'll update this post with my findings. The part that eludes me the most is the first section of code... finding the initial pay. It returns nothing. Any help in that area?

 

[pbaldy]

Is that all the code? What are you doing with the string?

 

[Xproterg]

The string is being loaded as a rowsource for a listbox.

I modified the code in two different ways, neither of which returns results. Can you see a difference here?

strSQL6 = "SELECT Students_Info.firstname, Students_Info.lastname FROM Students_Info 
 
WHERE ((#" & janumonthly & "# BETWEEN DateAdd("m",1,Students_Info.startONE) AND DateAdd("m",-1,Students_Info.gradONE)) and Students_Info.gradTWO Is Null) 
 
OR ((#" & janumonthly & "# BETWEEN DateAdd("m",1,Students_Info.startTWO) AND DateAdd("m",-1,Students_Info.gradTWO)) and Students_Info.gradTHR Is Null) 
 
OR ((#" & janumonthly & "# BETWEEN DateAdd("m",1,Students_Info.startTHR) AND DateAdd("m",-1,Students_Info.gradTHR));"

neither does this work...

strSQL6 = "SELECT Students_Info.firstname, Students_Info.lastname FROM Students_Info 
 
WHERE ((#" & janumonthly & "# BETWEEN DateAdd("m",1,Students_Info.startONE) AND DATE_SUB(Students_Info.gradONE, INTERVAL 1 Month)) and Students_Info.gradTWO Is Null) 
 
OR ((#" & janumonthly & "# BETWEEN DateAdd("m",1,Students_Info.startTWO) AND DATE_SUB(Students_Info.gradTWO, INTERVAL 1 Month)) and Students_Info.gradTHR Is Null) 
 
OR ((#" & janumonthly & "# BETWEEN DateAdd("m",1,Students_Info.startTHR) AND DATE_SUB(Students_Info.gradTHR, INTERVAL 1 Month));"

 

[pbaldy]

The way to debug this kind of thing is to add:

Debug.Print strSQL6

after the string is built but before setting the row source. Run the code, and that will print the finished SQL string to the VBA Immediate window. You can examine it there and perhaps spot the problem, or copy/paste that SQL to an empty query and try to run it. I'll tell you right now the double quotes in your formulas are causing a problem.

 

[Xproterg]

Yeah I noticed that right off the bat, and all doublequotes have been changed to single quotes in the dateadd functions. Where does the completed string print. I don't believe that I have that window open.

EDIT: Figured it out and this is what is returned-

SELECT Students_Info.firstname, Students_Info.lastname 
FROM Students_Info 
 
WHERE ((#1/1/2011# BETWEEN DateAdd('m',1,Students_Info.startONE) AND DATE_SUB(Students_Info.gradONE, INTERVAL 1 Month)) and Students_Info.gradTWO Is Null) 
 
OR ((#1/1/2011# BETWEEN DateAdd('m',1,Students_Info.startTWO) AND DATE_SUB(Students_Info.gradTWO, INTERVAL 1 Month)) and Students_Info.gradTHR Is Null) 
 
OR ((#1/1/2011# BETWEEN DateAdd('m',1,Students_Info.startTHR) AND DATE_SUB(Students_Info.gradTHR, INTERVAL 1 Month));

EDIT: Just tried the other method, and nothing returns in the debug screen.

Dim janu As String
Dim januyear As String
Dim janumonthly As Date
janu2 = "1/1/"
januyear = Text130.Value
janumonthly = janu2 & januyear

 
strSQL6 = "SELECT Students_Info.firstname, Students_Info.lastname FROM Students_Info 
 
WHERE ((#" & janumonthly & "# BETWEEN DateAdd(month,1,Students_Info.startONE) AND DateAdd(month,-1,Students_Info.gradONE)) and Students_Info.gradTWO Is Null) 
 
OR ((#" & janumonthly & "# BETWEEN DateAdd(month,1,Students_Info.startTWO) AND DateAdd(month,-1,Students_Info.gradTWO)) and Students_Info.gradTHR Is Null) 
 
OR ((#" & janumonthly & "# BETWEEN DateAdd(month,1,Students_Info.startTHR) AND DateAdd(month,-1,Students_Info.gradTHR));"

 
 
Debug.Print strSQL6
List141.RowSource = strSQL6

 

[pbaldy]

What does DATE_SUB do? If it's subtracting, you could have used DateAdd(), you just use a negative value.

 

[Xproterg]

datesub works as a subtraction. my recent edit shows what happens when using the negative value on dateadd. Still nothing. Hopefully its just stupidity on my part.

 

[pbaldy]

I think you had it right the first time:

DateAdd('m',1, startONE)

The interval is a string argument. Can you post the db here, or a representative sample?

 

[Xproterg]

I think you had it right the first time:

DateAdd('m',1, startONE)

The interval is a string argument. Can you post the db here, or a representative sample?

Yeah totally... don't mind the massive amounts of messy code. hehe

EDIT: Btw the month buttons on the third tab are the ones you're looking for.

 

[pbaldy]

There's no data in the student info table, did you strip it out or is this our problem? Hard to test with no data.

 

[Xproterg]

Sorry mate, had to. I populated the first record with just bleh-data. Should be functional now.