By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,490 Members | 1,753 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,490 IT Pros & Developers. It's quick & easy.

Date and Calendar help!

P: 16
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
Attached Files
File Type: zip Pay Period - Test.zip (14.7 KB, 67 views)
Nov 23 '11 #1

✓ answered by 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 :
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Form_Current()
  5.     Call GetPeriod
  6. End Sub
  7.  
  8. Private Sub txtAccomplishment_Date_AfterUpdate()
  9.     Call GetPeriod
  10. End Sub
  11.  
  12. Private Sub GetPeriod()
  13.     Dim strWhere As String, strDates As String
  14.  
  15.     With Me
  16.         If IsDate(.Accomplishment_Date) Then
  17.             strWhere = Format(.Accomplishment_Date, "\#m/d/yyyy\#") & _
  18.                        " Between [PYPD_BG] And [PYPD_END]"
  19.             strDates = Nz(DLookup(Expr:="[PYPD_BG] & '|' & [PYPD_END]", _
  20.                                   Domain:="[Pay Periods]", _
  21.                                   Criteria:=strWhere), "|")
  22.             .PYPD_BG = CDate(Split(strDates, "|")(0))
  23.             .PYPD_END = CDate(Split(strDates, "|")(1))
  24.         Else
  25.             .PYPD_BG = Null
  26.             .PYPD_END = Null
  27.         End If
  28.     End With
  29. 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).

Share this Question
Share on Google+
28 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
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:
Expand|Select|Wrap|Line Numbers
  1. Dim lngPeriod as long
  2. lngPeriod=nz(Dlookup("KEY_Period","tbl_WorkingPeriods","StartDate<#" & dt_WorkDate & "# AND EndDate>#" & dt_WorkDate & "#"),0)
  3. If lngPeriod=0 then
  4.   MsgBox "Work period could not be found, please try again"
  5. else
  6.   Msgbox "Work Period ID:" & lngPeriod
  7. End If
I hope you are able to take it from here, and convert it into working code for your application. Best of luck.
Nov 23 '11 #2

100+
P: 759
Or test using a query:
Attached Files
File Type: zip Pay Period - Solve 1.zip (18.4 KB, 58 views)
Nov 24 '11 #3

NeoPa
Expert Mod 15k+
P: 31,709
Smiley's line #2 could be written as :
Expand|Select|Wrap|Line Numbers
  1. lngPeriod = Nz(DLookup("[KEY_Period]", _
  2.                        "[tbl_WorkingPeriods]", _
  3.                        "(#" & Format(dt_WorkDate, "m/d/yyyy") & "# Between [StartDate] And [EndDate])), _
  4.                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
Nov 24 '11 #4

100+
P: 759
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...
Nov 24 '11 #5

NeoPa
Expert Mod 15k+
P: 31,709
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?
Nov 24 '11 #6

100+
P: 759
Oh, you suggest to change the table design.
Because in Cabnabs's post the Pay Period table has no PeriodID field.
** Snip **
Thank you.
Nov 25 '11 #7

TheSmileyCoder
Expert Mod 100+
P: 2,321
@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.
Nov 25 '11 #8

NeoPa
Expert Mod 15k+
P: 31,709
** 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.
Nov 25 '11 #9

100+
P: 759
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.
Attached Files
File Type: zip Pay Period - Solve 2.zip (55.7 KB, 51 views)
Nov 26 '11 #10

NeoPa
Expert Mod 15k+
P: 31,709
It didn't seem that way to me Mihail, but I have to assume you know what you're talking about (:confused:).
Nov 26 '11 #11

P: 16
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!
Nov 28 '11 #12

NeoPa
Expert Mod 15k+
P: 31,709
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.
Nov 28 '11 #13

P: 16
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?
Nov 28 '11 #14

NeoPa
Expert Mod 15k+
P: 31,709
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 :
Expand|Select|Wrap|Line Numbers
  1. ...
  2. FROM   [MainTable] AS tMT
  3.        INNER JOIN
  4.        [tblPeriod] AS tP
  5.   ON   tMT.Accomplishment_Date Between tP.[PYPD BG] And tP.[PYPD END]
  6. ...
Nov 28 '11 #15

P: 16
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!
Nov 29 '11 #16

P: 16
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).
Nov 29 '11 #17

NeoPa
Expert Mod 15k+
P: 31,709
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).
Nov 29 '11 #18

P: 16
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
Nov 29 '11 #19

NeoPa
Expert Mod 15k+
P: 31,709
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.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Form_Current()
  5.     Call GetPeriod
  6. End Sub
  7.  
  8. Private Sub txtAccomplishment_Date_AfterUpdate
  9.     Call GetPeriod
  10. End Sub
  11.  
  12. Private Sub GetPeriod()
  13.     Dim strWhere As String, strDates As String
  14.  
  15.     With Me
  16.         If IsDate(.txtAccomplishment_Date) Then
  17.             strWhere = Format(.txtAccomplishment_Date, "\#m/d/yyyy\#") & _
  18.                        " Between [PYPD BG] And [PYPD END]"
  19.             strDates = DLookup(Expr:="[PYPD BG] & '|' & [PYPD END]", _
  20.                                Domain:="[tblPayPeriod]", _
  21.                                Criteria:=strWhere)
  22.             .[txtPYPD BG] = CDate(Split(strDates, "|")(0))
  23.             .[txtPYPD END] = CDate(Split(strDates, "|")(1))
  24.         Else
  25.             .[txtPYPD BG] = Null
  26.             .[txtPYPD END] = Null
  27.         End If
  28.     End With
  29. End Sub
Nov 29 '11 #20

NeoPa
Expert Mod 15k+
P: 31,709
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.
Nov 29 '11 #21

P: 16
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
Nov 29 '11 #22

NeoPa
Expert Mod 15k+
P: 31,709
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.
Nov 29 '11 #23

P: 16
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
Attached Files
File Type: zip Activities Tracker - Test4.zip (29.2 KB, 57 views)
Nov 30 '11 #24

NeoPa
Expert Mod 15k+
P: 31,709
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 :
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Form_Current()
  5.     Call GetPeriod
  6. End Sub
  7.  
  8. Private Sub txtAccomplishment_Date_AfterUpdate()
  9.     Call GetPeriod
  10. End Sub
  11.  
  12. Private Sub GetPeriod()
  13.     Dim strWhere As String, strDates As String
  14.  
  15.     With Me
  16.         If IsDate(.Accomplishment_Date) Then
  17.             strWhere = Format(.Accomplishment_Date, "\#m/d/yyyy\#") & _
  18.                        " Between [PYPD_BG] And [PYPD_END]"
  19.             strDates = Nz(DLookup(Expr:="[PYPD_BG] & '|' & [PYPD_END]", _
  20.                                   Domain:="[Pay Periods]", _
  21.                                   Criteria:=strWhere), "|")
  22.             .PYPD_BG = CDate(Split(strDates, "|")(0))
  23.             .PYPD_END = CDate(Split(strDates, "|")(1))
  24.         Else
  25.             .PYPD_BG = Null
  26.             .PYPD_END = Null
  27.         End If
  28.     End With
  29. 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).
Nov 30 '11 #25

P: 16
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub GetPeriod()
  2. Me.PYPD_BG.Value = DLookup("PYPD_BG", "Pay Periods", "PYPD_BG<=#" & Me.Accomplishment_Date & "# AND PYPD_END>=#" & Me.Accomplishment_Date & "#")
  3.  
  4. Me.PYPD_END.Value = DLookup("PYPD_End", "Pay Periods", "PYPD_BG<=#" & Me.Accomplishment_Date & "# AND PYPD_END>=#" & Me.Accomplishment_Date & "#")
Dec 1 '11 #26

NeoPa
Expert Mod 15k+
P: 31,709
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.
Dec 1 '11 #27

P: 16
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.
Dec 2 '11 #28

NeoPa
Expert Mod 15k+
P: 31,709
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.
Dec 2 '11 #29

Post your reply

Sign in to post your reply or Sign up for a free account.