Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Combining a SQL statement in VBA

Posted on 2006-05-05
26
Medium Priority
?
179 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:graysoc
  • 15
  • 11
26 Comments
 
LVL 1

Author Comment

by:graysoc
ID: 16613787
PS> Thanks in advance!

G
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 2000 total points
ID: 16613830
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
 
LVL 54

Expert Comment

by:nico5038
ID: 16613841
Hmm, another question also pops up, does an absence of 3 days get recorded in three rows ?

Nic;o)
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 1

Author Comment

by:graysoc
ID: 16614111
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
 
LVL 54

Expert Comment

by:nico5038
ID: 16614166
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
 
LVL 1

Author Comment

by:graysoc
ID: 16614368
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
 
LVL 1

Author Comment

by:graysoc
ID: 16614455
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
 
LVL 54

Expert Comment

by:nico5038
ID: 16614584
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
 
LVL 54

Expert Comment

by:nico5038
ID: 16614608
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
 
LVL 1

Author Comment

by:graysoc
ID: 16614642
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
 
LVL 1

Author Comment

by:graysoc
ID: 16614693
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
 
LVL 54

Expert Comment

by:nico5038
ID: 16614804
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
 
LVL 54

Expert Comment

by:nico5038
ID: 16614888
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
 
LVL 1

Author Comment

by:graysoc
ID: 16614944
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
 
LVL 54

Expert Comment

by:nico5038
ID: 16615059
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
 
LVL 1

Author Comment

by:graysoc
ID: 16615069
Yeah, let me export it...

G
0
 
LVL 1

Author Comment

by:graysoc
ID: 16615228
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
 
LVL 54

Expert Comment

by:nico5038
ID: 16618398
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
 
LVL 1

Author Comment

by:graysoc
ID: 16649611
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
 
LVL 1

Author Comment

by:graysoc
ID: 16649735
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
 
LVL 1

Author Comment

by:graysoc
ID: 16649746
I realize now why your code wasn't working though.  Your dates are in reverse order from mine.  Oops.. :)
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16649986
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
 
LVL 1

Author Comment

by:graysoc
ID: 16649990
Thank you.  :)
0
 
LVL 1

Author Comment

by:graysoc
ID: 16650122
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
 
LVL 54

Expert Comment

by:nico5038
ID: 16650322
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

810 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