Question Return records based on date

[iidx036]

Hi all,

I hope someone can give me a few pointers on this question.

I have a table of people (called "workers") that can only work on certain days e.g. Joe can only work Monday and Thursday. These are set by having seven Yes/No fields called Monday to Sunday.

I have another table (called "jobs") that list jobs and one field has the date the job is due to start. I have a form for this table that has an unbound combo box to the other form "workers" that lists all of the workers names.

What I would like to do is when the job date is entered have the combo box only show the workers names that are available to work on the day the job is due to start.

If someone could point me in the right direction it would be most appreciated.

Many thanks,

Jay

 

[Rabbie]

For the data source for the combo box use a query that selects workers who are available on a specific day. Use the Weekday function (details in Access help) to find which day you need to check for.

 

[iidx036]

Hi Rabbie,

Thanks for the reply! I see where you are going but is it possible you could help me out a bit further. I apologise for my lack of understanding but this specific task has me really stumped?

Kind regards,

Jay

 

[WayPay]

Hi X36,

Have a look at the attached example.

BTW that

sWhere = " WHERE " & WeekdayName(Weekday(Me.ActiveControl)) & " = Yes "

will only work on English Windows. Use something like this

    Select Case Weekday(Me.ActiveControl)
        Case 1
            sWeekDayName = "Sunday"
        Case 2
            sWeekDayName = "Monday"
        Case 3
            sWeekDayName = "Tuesday"
        Case 4
            sWeekDayName = "Wednesday"
        Case 5
            sWeekDayName = "Thursday"
        Case 6
            sWeekDayName = "Friday"
        Case 7
            sWeekDayName = "Saturday"
    End Select

If you want something more robust and less elegant.

 

[iidx036]

Waypay,

You are a star thats perfect! I have added this into my database and it's great. However, when I pick a day, for example a Thursday, from the calendar picker it retrieves the records where Friday is YES. So, in a nutshell it returns the records from the next day from the date you pick in the calendar.

Any ideas? And thankyou very much for your time!

Cheers

Jay

PS - I have just checked your example and this does the same? Any ideas?

 

[WayPay]

Yeah, I have an idea but I don't like it . I just re-tested the example and it works fine. Does the JobDate field in the example display the correct weekday?

Oh yeah, the idea was that somehow your system is convinced that Saturday is the first day of the week. This

sWhere = " WHERE " & WeekdayName(Weekday(Me.ActiveControl),vbSaturday) & " = Yes "

might fix your problem (could also be vbSunday or vbMonday); my problem with this is that I wouldn't understand why the fix worked .

 

[iidx036]

Hi Waypay,

When I add the vbSaturday into the code I then get a pop saying "Enter Parameter Value". The dialog box show the wording "Sat" then a text is below that needing an entry.

Any ideas as to why?

Thanks again for your help.

Jay

UPDATE - Waypay, I added the SELECT CASE option to the code so it now reads:

If Not IsNull(Me.ActiveControl) Then

Select Case Weekday(Me.ActiveControl)
Case 1
sWeekDayName = "Sunday"
Case 2
sWeekDayName = "Monday"
Case 3
sWeekDayName = "Tuesday"
Case 4
sWeekDayName = "Wednesday"
Case 5
sWeekDayName = "Thursday"
Case 6
sWeekDayName = "Friday"
Case 7
sWeekDayName = "Saturday"
End Select

' construct WHERE clause
sWhere = " WHERE " & sWeekDayName & " = Yes "

End If

This now works perfectly - thankyou for all your time and effort on this its most appreciated.

Cheers

Jay

 

[WayPay]

Glad that helped, you almost had me stumped .

 

[iidx036]

No seriously thanks again.

One more thing though when I have selected a worker from the drop down box I would like to click on a button that would show a list of that workers assigned job dates.

Could you give me a steer on how that could be achieved?

Cheers

Jay

 

[WayPay]

What kind of list? I guess you want more than just a list of dates. You could put a subform on your form. There'll be a wizard to help.

 

[iidx036]

Waypay,

Ideally, I would like a popup box simply showing the date, worker and and a job number associated with that worker in the combo box.

Any thoughts?

Cheers again,

Jay

 

[WayPay]

Do you want all the jobs for that worker to show, or a specific one?

 

[iidx036]

Hi Waypay,

Ideally, showing all jobs a week before and a week after the date from the calendar picker field. But any help on this would be most appreciated.

Thanks again your a star!

Cheers

Jay

 

[WayPay]

My real job got in the way of coding up the example .

Have a look at the updated version; it probably does a bit more than you needed . Don't hesitate to ask about what you don't understand.

 

[iidx036]

Hi Waypay,

Yet again your help is outstanding!

This is exactly what I would like to do however, not this complex.

What I would like to do is not write the records for the worker or the job date but simply query and show (in the pop up box) the jobs that this worker has for the 2 week period either side of the chosen date. It is only at the point the rest of the form is filled in are the records saved to the Jobs table.

So, ultimately it is a manual sanity check for the database user to see the workers availibility.

One place I am falling down I suspect is that my Job table simply brings the Workers name in through an unbound combobox looking at the workers table. This is then written to the Jobs table when the record is saved. It doesn't have a workerID as your example has.

Thanks again,

Jay

 

[WayPay]

This is exactly what I would like to do however, not this complex.

I kinda felt that coming .

It doesn't have a workerID as your example has.

It should have one, then .

I'll rework the example to do it without saving the record. Just gimme a few 10's of minutes .

 

[WayPay]

Here's an updated example. I ran into some problem where there's no text in the combobox but it still has the previously selected ID for a value. I'll have to look into that, but I'm off to RL now .

 

[iidx036]

Waypay - thanks again for the help!

To get this to work I am going to need the WorkerID in both tables, you did say so

So, what I have done is to add a textbox to the form that brings in the workerID (the control source for this is set to column 1 from the combobox that brings in the worker names)

However, how do I write this value to the table where I can then use your updated example?

Sorry to be a pain

Thanks again,

Jay

 

[WayPay]

..how do I write this value to the table where I can then use your updated example?

That depends. Can you post a bit of sample code?

 

[iidx036]

Basically I have:

The main form (bound to the AWI table) which has the combo box (called cbfitter) which shows the workers names (from the table called fitters).

When this combo box changes a text box (called tbfitter_id) shows the worker ID number. This is done by having the text box control source set to:

=[cbfitter].[Column](1)

So what I would like to do is something like:

sSQL = " SELECT AWI.exact_date, AWI.job_number, AWI.fitter_name, AWI.job_duration, fitters.fitter_phone FROM AWI INNER JOIN fitters ON [Forms]![edit_job_awi]![tbfitter_id] = fitters.fitter_id ORDER BY AWI.exact_date " - This then returns the results for the message box as per your example.

However, I can't seem to get that to work properly? Can I use a text box on a form in a SQL comand like this as it doesn't seem to work.

Thanks again,

Jay