Matching several criteria for date and time

[victorl]

Hi!

I am about to create a workdatabase that will let directors submit work into it and employees to get a report by filling in details in a form as well.

Then I also have multiple fields of time format that will need to work in the selection form to create a correct report for that employee and the current date.

What I mean is that I want access to check the following when I fill in the date: Which weekday it is; which workpass it is (PM, AM, Night), if it is the first day of the month etc...

Also it will be great if someone know how to make an database entry copying itself and have a regular number increase, for example 5 10 15 20 25 30 and so on.

Thans in advance!

[beacon]

Hi Victor, welcome to Bytes!

It sounds like you will need at least 2 different, but would probably be served using 3 different forms (in my opinion); one for the directors to enter their data, one for the employees to enter their data, and a form that will allow employees or directors to enter parameters for reports they want to run (you would probably want one of these parameter forms for each report).

For the date/time items you've requested, there are functions that you can use as expressions directly in controls that you put on your form, or as VBA behind the controls on the form. Go to this Microsoft Office site and check out the 'WeekdayName', 'MonthName', and 'TimeValue' functions that are available (or you can search for them on Google or Bing since the examples are somewhat limited).

For the first day of the current month, there's an example VBA formula on the MSDN site.

I'm somewhat confused by what you're asking in your last question, but if I understand it correctly, you will have to have a field with a number data type on your table need to use VBA in your form to search for the maximum number and then add 5 to it.

beacon

[victorl]

@beacon
Hi Beacon!

Thanks a lot for taking time answering my questions.
In fact I already created a few forms. One for data enter and one for data removal. Actually the form I could not really figure out how to code is about the last one with parameters for report.

I will be looking further into the time parameters to figure out how to get the time to work correctly. For example: I add an issue the 3rd May, and prefers it to repeat once a month, the report should show the same issue again for the report due on 3rd June.

My last question was pointing out the above mentioned issue. Lets say the director enter a new field on 1st september. He will have a listbox in my form to choose how often this will appear in the daily report. If he creates an issue that will occur every 14 days, I want it to appear on the report for 15 sept, 29 sept, 13 oct, 27 oct and so on.

Best Regards
/Victor

[beacon]

If he creates an issue that will occur every 14 days, I want it to appear on the report for 15 sept, 29 sept, 13 oct, 27 oct and so on.

For the above, check out the DateAdd function. You can use it to accept the date entered by the director and determine what the next date will be in a specific number of days, months, quarters, or years. You can use this against the value in the previous field to determine the next 14 days, so on and so on forever or until an end date if you identify one (you'll likely need another field for the end date).

I will be looking further into the time parameters to figure out how to get the time to work correctly. For example: I add an issue the 3rd May, and prefers it to repeat once a month, the report should show the same issue again for the report due on 3rd June.

For the above, I meant to provide you with a link to an awesome website that details how to create custom forms that pass parameters to the query that your report is based on. Anyway, here's the LINK.