Combining a SQL statement in VBA

Hello all,

I have a little code I want to run when a user adds an absence to the absence table.  I want to track how many days they've been absent in the last year.

So I want the following code to give me a count, which then determines which code goes in a text box.  (3 in 6, 4 in 12, 5 in 12, 6 in 12, 7 in 12)  3 in 6 = 3 absences in 6 months, etc....

I know this isn't nearly correct syntax, but that's what I need the help with!  :)

Dim Today as Date
Dim AbsenceCount as String
Dim ThreeAbsence
Today = format(now() "mm/dd/yyyy")

     'How do I get the today variable into the SQL as below?
ThreeAbsence = "select count(*) from tbl_absence where start_date = Today - Format(DateAdd("mm", -6, Date)"
AbsenceCount = "select count(*) from tbl_absence where start_date = Today - Format(DateAdd("yyyy", -1, Date)"

If ThreeAbsence = 3 then
   form_subform_absence.absence_flag = "3 in 6"
End If

Select Case AbsenceCount
    Case  4
    form_subform_absence.absence_flag = "4 in 12"
    Case  5
    form_subform_absence.absence_flag = "5 in 12"
    Case  6
    form_subform_absence.absence_flag = "6 in 12"
    Case  7
    form_subform_absence.absence_flag = "7 in 12"
End Select
LVL 1
graysocAsked:
Who is Participating?
 
nico5038Commented:
It's best to have a separate report to show the absence for all since today, but when adding a new record you can show on your form the numer of "hits" using a field with:
=Dcount("*","tbl_absence","start_date between #" & DateAdd("mm", -6, Date()) & "# and #" & date() & "#")
and for a year:
=Dcount("*","tbl_absence","start_date between #" & DateAdd("y", -1, Date()) & "# and #" & date() & "#")

Just wondering or you don't need "schoolyears" ?

Nic;o)
0
 
graysocAuthor Commented:
PS> Thanks in advance!

G
0
 
nico5038Commented:
Hmm, another question also pops up, does an absence of 3 days get recorded in three rows ?

Nic;o)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
graysocAuthor Commented:
The way we view absences is singularly.  For instance, it doesn't matter if you were out for 1 day or 4 days, it still counts as one "occurrence."  That fact makes this much easier.

No, the year is measured in regular years.  We're not a school.  :)

So help me with what you are telling me from above.  I should create a new field, and have it equal to the above formula?

G
0
 
nico5038Commented:
Hmm, bit puzzled why you check both six months and a year and report this in one absence_flag field.
The "3 in 6" will e.g. be overwritten by "4 in 12" when this happens.

Best to have two separate fields for this and generate the message like:

   form_subform_absence.absence_flag6M = Dcount("*","tbl_absence","start_date between #" & DateAdd("mm", -6, Date()) & "# and #" & date() & "#") & " in 6"

    form_subform_absence.absence_flag1Y = Dcount("*","tbl_absence","start_date between #" & DateAdd("y", -1, Date()) & "# and #" & date() & "#") & " in 12"

Idea ?

Nic;o)

0
 
graysocAuthor Commented:
Well, this event happens when the "add absence" button is clicked, so the field will be specific to the one absence.  Which makes me think that I'll need to lower the number by one, because when you add an absence it will be the number of old absences + todays absence.

We generate letters based on how many occurrences they have.  They are allowed 3 in 6, then the 4th absence becomes 4 in 12, 5 in 12, 6 in 12, then 7 in 12.  On the 7th, they are fired.

There is another problem also.  If the absence is approved, it should not be counted.  I'll need to check for that as well.  

I see what your question is now that I read it more carefully.  If they have 3 absences in the past 6 months, then they get a 3 in 6 marker.  But if they have more than 3 in the past 12 months, they get a 4 in 12, etc.  So, it should be one field.  Does that make sense?

Any suggestions about the approved absences?

G



0
 
graysocAuthor Commented:
The above code doesn't seem to be working properly.  It keeps saying 4 in 12, even though I have 6 absences in April of this year for my test user...  

G
0
 
nico5038Commented:
Guess the new absence needs to be counted and when you have  a form with employee and a subform with the absence(s) then you can use the Dcount() field on the employee form. It will be corrected automatically when the new absence is entered in the subform.
For the approved absence use in the Dcount a test like:
Dcount("*","tbl_absence","[approved] = False and start_date between #" & DateAdd("mm", -6, Date()) & "# and #" & date() & "#")
This will only count "Not" approved absences.

What's a "3 in 6 marker" ? Some letter that need to be printed ?

Also keep in mind that the count can be the next day one lower as the oldest absence can "drop off".
This implies that the "markers" should always be calculated with a certain "base date" (here you use the systemdate) and not recorded as they are "stale" the next day.....

Clearer ?

Nic;o)
0
 
nico5038Commented:
Hmm, you're probably dealing with a date format problem, to be sure the code works properly try:
Dcount("*","tbl_absence","start_date between #" & format(DateAdd("y", -1, Date()),"yyyy/mm/dd) & "# and #" & format(date(),"yyyy/mm/dd) & "#") & " in 12"

Nic;o)
0
 
graysocAuthor Commented:
Yes, letters are printed or people are called in to their supervisor depending upon how many absences they've had in the previous year.  

I understand about "stale" dates, but I think I don't mind storing them along with the absence entry.  We can look back and see what sort of absence that was at the time it was entered.  The most recent absence will have the correct attendance record.

I will work with this more, but I think we're running into a misunderstanding of what I need.  Why can't I just do something along the lines of:

Form_subform_absence.txt_attendance = "select count(*) from tbl_absence where start_date between#" & DateAdd("y", -1, Date) & "# and #" & Date & "# AND approved_by is not null"

Is SQL not allowed in this way?

G

0
 
graysocAuthor Commented:
Dcount("*","tbl_absence","start_date between #" & format(DateAdd("y", -1, Date()),"yyyy/mm/dd) & "# and #" & format(date(),"yyyy/mm/dd) & "#") & " in 12"


This one is running into an error with parentheses, or the like.  They appear to all be in place to me.

G
0
 
nico5038Commented:
Try:

Dcount("*","tbl_absence","start_date between #" & format(DateAdd("y", -1, Date()),"yyyy/mm/dd") & "# and #" & format(date(),"yyyy/mm/dd") & "#") & " in 12"

Nic;o)
0
 
nico5038Commented:
Hmm to be entirely correct you would need to correct the "startdate" with one day as the between works inclusive so:

Dcount("*","tbl_absence","start_date between #" & format(DateAdd("y", -1, Date())+1,"yyyy/mm/dd") & "# and #" & format(date(),"yyyy/mm/dd") & "#") & " in 12"

Thus you'll get today: 2005/05/06 till 2006/05/05
as 2005/05/05 is more than a year in the past...

Nic;o)
0
 
graysocAuthor Commented:
It is still returning zero.  I've tested it with several records, and the dates are valid, and between the range posted.

Thank you for your help.

G
0
 
nico5038Commented:
Can you upload a small zipped sample .mdb so we can have a look ?
(Use www.ee-stuff.com and post the link provided here)

Nic;o)
0
 
graysocAuthor Commented:
Yeah, let me export it...

G
0
 
graysocAuthor Commented:
Ok, so it is not working too well, having all of the links not present, etc, but you should be able to see what I'm doing...lol

If you need assistance with my backward logic, please let me know.

G
0
 
nico5038Commented:
The form isn't really working, just put a .mdb with your testdata in tbl_absence so I can check the Dcount()

Nic;o)
0
 
graysocAuthor Commented:
I'm trying to think of another way to do this.  Right now, I think the Dcount is looking at the wrong data somehow, and getting 0.  (I think it is looking at the current record, not the entire dataset, perhaps.)  

Could I run a query when a start date is entered that would tell me how many absences they've had in the past year?  Could a MsgBox then pop up with the count of absences in the past year?

G
0
 
graysocAuthor Commented:
I think I found an equitable solution.  It works well enough.

G

Private Sub cmd_add_absence_Click()
Dim Today As Date
Dim rst As Recordset
Dim Db As Database
Dim StrSQL As String

StrSQL = "select * from tbl_absence where tbl_absence.start_date between #" & Format(DateAdd("yyyy", -1, Date), "mm/dd/yyyy") & "# AND #" & Format(Date, "mm/dd/yyyy") & "# AND approved_by is null AND co_id = " & Me.co_id & ""


Set Db = CurrentDb
Set rst = Db.OpenRecordset(StrSQL)

Me.Absence_Information.SetFocus

Form_subform_absence.AllowAdditions = True
DoCmd.RunCommand acCmdSubformFormView
DoCmd.GoToRecord , , acNewRec

  'this msgbox gives me the count I want.  (Previous absences in the year, plus this one.
  'I'm just going to make the field = rst.recordcount + 1 & "In 12", etc.
 MsgBox rst.RecordCount + 1


End Sub
0
 
graysocAuthor Commented:
I realize now why your code wasn't working though.  Your dates are in reverse order from mine.  Oops.. :)
0
 
nico5038Commented:
The "famous" US date format that's causing trouble again, normally a query requires that format, but the regional settings can show them differently.
Thus my use of the YYYYMMDD format as Access will automatically translate it in the propoer dat.

Glad it's solved now and success with your application !

Nic;o)
0
 
graysocAuthor Commented:
Thank you.  :)
0
 
graysocAuthor Commented:
ThreeAbsence = DCount("*", "tbl_absence", "start_date between #" & DateAdd("mm", -6, Date) & "# and #" & Date & "# And co_id = " & Me.co_id & "and approved_by is null") + 1

AbsenceCount = DCount("*", "tbl_absence", "start_date between #" & DateAdd("yyyy", -1, Date) & "# and #" & Date & "# And co_id = " & Me.co_id & "and approved_by is null") + 1

Another quick question...

The AbsenceCount (the second one) works, but the first one doesn't.  I just copied the absence count, then changed the dateadd("yyyy", -1, date) to  DateAdd("mm", -6, Date)  For some reason it doesn't work.  It get "invalid call or procedure."  And ideas what might be going on?

G

0
 
nico5038Commented:
Try:
DateAdd("m", -6, Date)
Don't ask me why Year is YYYY and Month just M, but that's the way as it's defined. Just select the DateAdd and press F1 to see the possibilities :-)

Nic;o)
0
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.

All Courses

From novice to tech pro — start learning today.