Date and Calendar help!

[Cabnabs]

Ive used this website to design a database for my current job that tracks the activities of our analyst. I have solved all my VBA problems using this website. THANKS! I just having one last problem.

Essentially, I want the analyst to choose a date of their activity in a form and Access recognize what pay period it falls into. How do I do this? I have a table which shows the Beginning pay period dates and ending pay period dates. Is this a simple dlookup? Im lost, any help would be appreciated.

I attached a table for the example. Thanks in Advance!
Can

[TheSmileyCoder]

Should be a simple matter. Lets assume dt_WorkDate is the variable containing your work date, and your work period table has a primary autonumber key named KEY_Period.


This would be the example to go with that:

Dim lngPeriod as long
lngPeriod=nz(Dlookup("KEY_Period","tbl_WorkingPeriods","StartDate<#" & dt_WorkDate & "# AND EndDate>#" & dt_WorkDate & "#"),0)
If lngPeriod=0 then
  MsgBox "Work period could not be found, please try again"
else
  Msgbox "Work Period ID:" & lngPeriod
End If

I hope you are able to take it from here, and convert it into working code for your application. Best of luck.

[Mihail]

Or test using a query:

[NeoPa]

Smiley's line #2 could be written as :

lngPeriod = Nz(DLookup("[KEY_Period]", _
                       "[tbl_WorkingPeriods]", _
                       "(#" & Format(dt_WorkDate, "m/d/yyyy") & "# Between [StartDate] And [EndDate])), _
               0)

In any event, it should be rewritten to take account of SQL literal date formatting and dates which are equal to the period start and end dates.

PS. This is not a criticism (except in the most literal sense) of Smiley's very good answer

[Mihail]

Sorry, TheSmileyCoder and NeoPa.
It looks as I see other database than you.
Where is the ID for Period ? (PeriodID)
I am not very familiar with SQL but...

[NeoPa]

The field [KEY_Period] (as explained in Smiley's post #2) is the ID of the period and is a long value field in the table. Does that answer your question?

[Mihail]

Oh, you suggest to change the table design.
Because in Cabnabs's post the Pay Period table has no PeriodID field.
** Snip **
Thank you.

[TheSmileyCoder]

@Mihail
I have not looked at the attachment.
In my oppinion questions should be asked without the need to download an attachment. I appreciate that there may be times when its simply not possible to explain the issue without an attachment, but I dont think this question is such a case, explaining a table design is a simple matter.

I am also unwilling to download attachments unless its from someone I have built at least a minimum level of trust with.

[NeoPa]

** snip **

Smiley's response (quite correctly) was not based on anything that wasn't clearly visible in the question and available to anyone browsing through the thread. Browsing (as the great majority of people who visit a thread do) does not include following any links (attachments or otherwise) found therein. As the layout of the data was not included in the question Smiley was obliged to indicate an example for himself, which all who browse could follow and understand.

@Smiley
Absolutely. The posting instructions are clear that the question needs to be explained adequately. Posting an attachment is no part of an explanation and I generally ignore them entirely unless I, or another expert or responder, has specifically requested they be included. Otherwise too much expert time is wasted on a single thread simply to allow the OP to avoid the trouble of formulating their question properly. This, in turn, means that other threads and members get less than their fair share of the resources available.

[Mihail]

Hello, NeoPa.
I fully understand why you delete one of my post.
Unfortunately you delete the good one and you keep the wrong one.
THIS post contain the GOODE attachment.

[NeoPa]

It didn't seem that way to me Mihail, but I have to assume you know what you're talking about (:confused:).

[Cabnabs]

All, Thanks for your Reponses but I need a little more help. I'm still confused. I have a form titled productivity. The table to this form, has a column for accomplishment_date, a column for Beg Pay Period Date (PYPD BG) and a column for ending Pay period date (PYPD END). What I want is when someone selects an accomplishment date from the date picker, the pay period dates fill in. I have all the pay periods in a seperate table. Is this even possible, considering the pay period dates are in two separate columns. Ughh- Please help! Thanks for your assistance!

[NeoPa]

What you are requesting is help to make your database non-normalised (See Database Normalisation and Table structures). Fundamentally, it is not a good idea to save data which is already able to be determined from existing data.

My recommendation would be to remove these fields from your table and determine them dynamically whenever required.

[Cabnabs]

Hi NeoPa, any suggestions on how to perform this task? Is there anyway to select a random date and Access regognize which pay period it is?

[NeoPa]

Yes. Both Smiley's and my posts (#2 & #4) indicate one way of determining the correct period. Typically though, it would make more sense in a query to include an INNER JOIN to the table such as follows :

...
FROM   [MainTable] AS tMT
       INNER JOIN
       [tblPeriod] AS tP
  ON   tMT.Accomplishment_Date Between tP.[PYPD BG] And tP.[PYPD END]
...

[Cabnabs]

NeoPa, I guess I'm confused because I'm not sure where to put the code. I'm decent at Access but this is my first database that I've had to venture beyond the built-in features.

I have tbl Productivity that has the Accomplishment_date and a form that feeds into this table. I have a separate table that has the pay periods listed. When an analyst enters the accomplishment_date in the form, I would like access to recognize the pay period date.

Please help me figure this out - where am I putting the code? I can upload a dummy file if needed.

I can't tell you how much I appreciate this!

[Cabnabs]

NeoPa, I thank you so much for your help. I know you are not a fan of attachments but I need direct help. I dont understand the responses. Ive done some basic coding to regognize computer name and filter combo boxes and a few macros. So some of the responses are above my knowledge level.

Can you please take a look at the dummy file and help me with the code (sorry for begging - Im stressed due to this deadline).

[NeoPa]

I'm happy to have a look at it for you, and I appreciate you're a little confused by the responses (Tip: This can be markedly reduced by having real clarity in the question. Difficult for a newbie I appreciate, but definitely worth every effort you can put into it. That's for the future though.), but to post an attachment properly so that we can help you as well as possible you will need to follow the instructions in Attach Database (or other work) (I can't open ACCDB files - but there are other very important issues found in there too).

[Cabnabs]

Thanks for understanding NeoPa. I've figured out most of the basic coding for username and combo box selections based on a previous selection but this has got me stumped and the deadline to present this isn't helping.

I followed the directions on the link and I attached the revised zip file. Thank you in Advance

[NeoPa]

CabNabs:
I have tbl Productivity that has the Accomplishment_date and a form that feeds into this table. I have a separate table that has the pay periods listed. When an analyst enters the accomplishment_date in the form, I would like access to recognize the pay period date.

In a situation such as this what needs to happen is that whenever a date is entered onto the form for the field [Accomplishment_Date] (in a bound control of whatever name) then code needs to be triggered to find the start and end dates for the corresponding period from the Pay Periods table (For convenience, and because we don't know its name from you, we'll refer to this as [tblPayPeriod]). Essentially, where I have names from you I've used them, otherwise I've created names that seem appropriate and you'll have to match these names to those in your database to get the code to work for you.

NB. To ensure any existing records also reflect the correct dates for the Pay Periods we also need to invoke the main routine (GetPeriod) from the Form_Current() event procedure.

Option Compare Database
Option Explicit
 
Private Sub Form_Current()
    Call GetPeriod
End Sub
 
Private Sub txtAccomplishment_Date_AfterUpdate
    Call GetPeriod
End Sub
 
Private Sub GetPeriod()
    Dim strWhere As String, strDates As String
 
    With Me
        If IsDate(.txtAccomplishment_Date) Then
            strWhere = Format(.txtAccomplishment_Date, "\#m/d/yyyy\#") & _
                       " Between [PYPD BG] And [PYPD END]"
            strDates = DLookup(Expr:="[PYPD BG] & '|' & [PYPD END]", _
                               Domain:="[tblPayPeriod]", _
                               Criteria:=strWhere)
            .[txtPYPD BG] = CDate(Split(strDates, "|")(0))
            .[txtPYPD END] = CDate(Split(strDates, "|")(1))
        Else
            .[txtPYPD BG] = Null
            .[txtPYPD END] = Null
        End If
    End With
End Sub

[NeoPa]

Obviously, I started work on my last post after your post #17 and before your post #19. As such, I suspect there is no further need for me to look at your attachment (even after you went to all the trouble - sorry for that). However, if you still don't manage to get this resolved then I can look at it for you. Let me know, as I'm not sure what further I could help with if my latest post (#20) doesn't do it for you.

[Cabnabs]

Hi NeoPa, I cant get the code to work properly. Can you take a look at the database and insert the code - what am I missing?

Thanks in Advance

[NeoPa]

I'll do that, but I'd like you to apply what you can of what's been posted already and attach that version first. You won't learn anything by my simply doing it for you, and I need to see where it is you get confused if I'm to explain the problem to you properly.

PS If at any time you want any previous attachments deleted then just let me know and I'll sort that out for you.

[Cabnabs]

Okay NeoPa, I am uploading the version Ive been working in. Ive tried to do a dlookup in a seperate text box as well as try the vba coding. This project is due in a few days and I signed up for a Access:Advance class (Using VBA etc). Honestly I just dont understand the complex codes. I got the basics of coding but im having trouble building on that and there is no way Ill figure it out in a few days.

Also you can delete all the other attachments posted in this thread.

Thanks In Advance

[NeoPa]

Well, this is frustrating. The names you posted don't match the names in your database, and the code that you have in there is neither copied from my post nor does it compile. These are not the results of a lack of Access experience and I don't appreciate having to tidy up such problems frankly.

I changed the code to match your database, and here it is. Copy and Paste only to use it. If you attempt to use any other method you will certainly have even more problems :

Option Compare Database
Option Explicit
 
Private Sub Form_Current()
    Call GetPeriod
End Sub
 
Private Sub txtAccomplishment_Date_AfterUpdate()
    Call GetPeriod
End Sub
 
Private Sub GetPeriod()
    Dim strWhere As String, strDates As String
 
    With Me
        If IsDate(.Accomplishment_Date) Then
            strWhere = Format(.Accomplishment_Date, "\#m/d/yyyy\#") & _
                       " Between [PYPD_BG] And [PYPD_END]"
            strDates = Nz(DLookup(Expr:="[PYPD_BG] & '|' & [PYPD_END]", _
                                  Domain:="[Pay Periods]", _
                                  Criteria:=strWhere), "|")
            .PYPD_BG = CDate(Split(strDates, "|")(0))
            .PYPD_END = CDate(Split(strDates, "|")(1))
        Else
            .PYPD_BG = Null
            .PYPD_END = Null
        End If
    End With
End Sub

Currently though, your setup has so many problems I don't even have the time to list them all. Here's a few I remember though :

1. The data in [Pay Periods] doesn't even cover existing data in [Preparations].
2. There are various properties of the form [Preparations] that make no sense.
   1. DataEntry was Yes.
   2. NavigatingButtons was No.
3. You still have [PYPD_BG] and [PYPD_END] bound to fields in your table.

As I say, there may well be more items which were covered in the thread but which you haven't implemented in your database as well as others you simply wouldn't have known about due to lack of experience and understanding.

I can say that after tidying up my copy I was able to get the fundamental process working. Be careful though, this is not (I repeat not) a way to update the fields in your table (which, as I've covered a couple of times already now, should not even exist in that table - See the link in post #13).

[Cabnabs]

Thanks NeoPa for your slam/help, I really appreciated it. I took your advice and used your code as a starting point and redesigned my database and I found a solution that works, after burying my head in my keyboard for 4 hours. I used the below code to find the pay periods. ANd it works. Thanks all for your advice! My database is ready to go and I will do a live demo today.

Private Sub GetPeriod()
Me.PYPD_BG.Value = DLookup("PYPD_BG", "Pay Periods", "PYPD_BG<=#" & Me.Accomplishment_Date & "# AND PYPD_END>=#" & Me.Accomplishment_Date & "#")
 
Me.PYPD_END.Value = DLookup("PYPD_End", "Pay Periods", "PYPD_BG<=#" & Me.Accomplishment_Date & "# AND PYPD_END>=#" & Me.Accomplishment_Date & "#")

[NeoPa]

You're welcome of course. Though I'm surprised and impressed you recognise the benefit of the slam part already. Most people only realise how that benefits them further down the line. Kudos to you.

I'm somewhat confused as to why you chose to change the code though. There are always many ways to crack an egg, but although your version does cover much of the situation, and would probably get past casual tests, it's not as robust or tidy as the version I posted. It also requires two DLookup() calls (which means processing through the whole table an extra time). You do need to be careful of the way you deal with your date control though. Just appending the control value into the string with hash chars (#) around it is not correct. It will work correctly all the time if run within the USA or any other region using the same m/d/yyyy date format, as that is the standard for SQL, but it will never be fully portable like that (See Literal DateTimes and Their Delimiters (#) for the full info). Other than that, your criteria for the DLookups() is exactly equivalent to the one I suggested. Even though it looks quite different the effects are exactly the same (barring any confusion over date formats).

I can see why you might choose to recode it to fit your own understanding (I generally do that myself when I take other code onboard), but I hope you understand the limitations. I actually think you're doing the right thing in that respect, but just be aware you may fall over the problems later.

PS. Let me just add :

Cabnabs:
I took your advice and used your code as a starting point and redesigned my database and I found a solution that works, after burying my head in my keyboard for 4 hours.

With an attitude like that you will go far. Clearly you're at an early stage but you'll develop quickly with such an approach and the early days are always those which are the most confusing.

[Cabnabs]

NeoPa, This database will be used internally (about 7 users local to my office). I was scared to use your coding because I did not understand it fully. I am taking an Access class (focusing on Intermediate/Advance features) in two weeks and I plan to redesign the entire database to accomodate what I learned. Im sure after spending two weeks in class, your code will make sense to me.

[NeoPa]

That makes a lot of sense. See you in a fortnight then, when you can start showing off some of your new-found skills.

I'm sure you'll enjoy the course. Good luck with it.