Solved Updating Table with sql for dates

[murray83]

so i have a form where peopels holidays they have reuested, are then approved by managment, which then in turn updates one table as shown below in the code

Private Sub cmdUpdate_Click()

'removes the msg box asking are you sure you want to update the record
DoCmd.SetWarnings False


'updates the record in main table to say its been aproved and by whom and when
If Me.Approved.Value = " " Then
DoCmd.RunSQL ("Update tblRequestedDates set [Approved] = cmbAproved.Value , [ApprovedBy] = cmbAprovedBy.value, [DtApproved] = DtApproved.Value where [ID]= ID.value ")
DoCmd.OpenReport "HolidayFormApproved", acViewPreview   'print out a new holiday form with Approvers signature on
Else
MsgBox "You can only Approve Holidays which havent yet been approved"
End If




'sql to update rota to add H to the days of holiday
'DoCmd.RunSQL





Me.Refresh

'warnings back on
DoCmd.SetWarnings True
End Sub

But what i also want to do is update the main rota table called (tblRota) so for the dates in question it changes the shift record (Shift) to H ( for Holiday ) so when viewing the rota it would show that that person is on holiday, but the bit i cant get my head around which i am guessing will have steep curve for me to learn, is this.

how do i get the dates in the middle of the holiday as i'm just capturing the start and end date as see in image below, all help much appreciated

 

[theDBguy]

Hi. I've had a similar conversation recently. Let me see if I can find it for you.

 

[theDBguy]

Hi. I've had a similar conversation recently. Let me see if I can find it for you.

Well, no wonder this sounded familiar. This is what I found. You also started that thread.

Solved - find missing date gaps

so have a front end which is just used to find and open a PDF file, bit quicker and better looking then searching through file upon file. but have come upon a question that I'm sure can be answered by you smart lot, have searched here but didn't find anything on the subject. but what i would...

www.access-programmers.co.uk

 

[murray83]

not quite im not looking for missing dates, but for this instacne, collegue takes holiday on 10/7/2020 and is back on 20/07/2020 thats 10 days but the DB only gets the start date (StartDt) and end date (EndDt)

so are you saying could use same thing for the dates in between and then use them to update the rota table ????

 

[theDBguy]

not quite im not looking for missing dates, but for this instacne, collegue takes holiday on 10/7/2020 and is back on 20/07/2020 thats 10 days but the DB only gets the start date (StartDt) and end date (EndDt)

so are you saying could use same thing for the dates in between and then use them to update the rota table ????

Yes, that's exactly what I am saying. If you only have a start and end dates, then you are missing the dates in between, no?

 

[murray83]

could you show me how, if you dont mind

tables are included

so there is one holiday to approve, click admin then approve holiday to get to the form

 

[theDBguy]

could you show me how, if you dont mind

tables are included

so there is one holiday to approve, click admin then approve holiday to get to the form

Sorry, I hope you don't mind. It will have to wait until I get in front of a computer today.
Sent from phone...

 

[murray83]

Sorry, I hope you don't mind. It will have to wait until I get in front of a computer today.
Sent from phone...

that is fine, apperciate you even looking at my post

cheers

 

[murray83]

how about something like this

'sql to update rota to add H to the days of holiday once it has been approved
If Me.Approved.Value = "Yes" Then
DoCmd.RunSQL ("Update tblRota set [Shift] = 'H'  where [Colleague] = Colleague.Value and [Dt]= StartDt.Value between [Dt]= EndDt.Value ")
Else
End If

as i tried with out the beween and just the start date and it worked and placed a H on the shift for that person, which is ace, but then thought if i used inbetween and both dates for the Where bit of the Sql but nope

see attached

 

[theDBguy]

how about something like this

'sql to update rota to add H to the days of holiday once it has been approved
If Me.Approved.Value = "Yes" Then
DoCmd.RunSQL ("Update tblRota set [Shift] = 'H'  where [Colleague] = Colleague.Value and [Dt]= StartDt.Value between [Dt]= EndDt.Value ")
Else
End If

as i tried with out the beween and just the start date and it worked and placed a H on the shift for that person, which is ace, but then thought if i used inbetween and both dates for the Where bit of the Sql but nope

see attached

Sorry, I'm working on something else at the moment. I'll try later...

 

[Gasman]

how about something like this

'sql to update rota to add H to the days of holiday once it has been approved
If Me.Approved.Value = "Yes" Then
DoCmd.RunSQL ("Update tblRota set [Shift] = 'H'  where [Colleague] = Colleague.Value and [Dt]= StartDt.Value between [Dt]= EndDt.Value ")
Else
End If

as i tried with out the beween and just the start date and it worked and placed a H on the shift for that person, which is ace, but then thought if i used inbetween and both dates for the Where bit of the Sql but nope

see attached

syntax is BETWEEN startDate AND enddate

No = anywhere in the syntax.

 

[murray83]

syntax is BETWEEN startDate AND enddate

No = anywhere in the syntax.

like this ??

'sql to update rota to add H to the days of holiday once it has been approved
If Me.Approved.Value = "Yes" Then
DoCmd.RunSQL ("Update tblRota set [Shift] = 'H'  where [Colleague] = Colleague.Value And [Shift] BETWEEN StartDt.Value And EndDt.Value ")
Else
End If

 

[Gasman]

like this ??

'sql to update rota to add H to the days of holiday once it has been approved
If Me.Approved.Value = "Yes" Then
DoCmd.RunSQL ("Update tblRota set [Shift] = 'H'  where [Colleague] = Colleague.Value And [Shift] BETWEEN StartDt.Value And EndDt.Value ")
Else
End If

Well that is the correct syntax.
However it also only works if you use the correct fields?
How can [Shift] hold a date value if you are going to set it to "H" ?

 

[murray83]

doh

wondered why wasn't working, meant to be Dt

thinks i need a screen break hehe, cheers

 

[murray83]

last reply on this and i could bloody hug you

woohooo does a chair spin ( it works ) so this is the code if anyone is intrested

'sql to update rota to add H to the days of holiday once it has been approved
If Me.Approved.Value = "Yes" Then
DoCmd.RunSQL ("Update tblRota set [Shift] = 'H'  where [Colleague] = Colleague.Value And [Dt] BETWEEN StartDt.Value And EndDt.Value ")
Else
End If

cheers for all help guys, this be closed

 

[theDBguy]

last reply on this and i could bloody hug you

woohooo does a chair spin ( it works ) so this is the code if anyone is intrested

'sql to update rota to add H to the days of holiday once it has been approved
If Me.Approved.Value = "Yes" Then
DoCmd.RunSQL ("Update tblRota set [Shift] = 'H'  where [Colleague] = Colleague.Value And [Dt] BETWEEN StartDt.Value And EndDt.Value ")
Else
End If

cheers for all help guys, this be closed

Hi. Congratulations! Good luck with your project.

 

[Gasman]

doh

wondered why wasn't working, meant to be Dt

thinks i need a screen break hehe, cheers

I guessed that , however you will remember this more now as you had to look at what was wrong, rather than just being told. Admittedly you had a hint, but you still had to do some thinking.

There is always a case for taking a break if you have been on something for quite a while. That applies to physical tasks as well, not just coding.
Often when you come back, you see/overcome the problem.

I remember working 24 hours non stop one time when in the Merchant Navy. Myself and another engineer could not get a horizontal pump back in to it's housing, even after about 30 minutes trying. We went to bed and tried again the next morning. Went straight in.

Well done.