Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    May 2004
    Posts
    80

    Unanswered: Seasons again - almost fixed but how to get a non-specific year?

    Right, I used a nested IIF statement to determine the SeasonName from the DateAdded field, using Between....And......

    =IIf([DateAdded] Between #1/12/2004# And #31/12/2004#,"Summer",IIf([DateAdded] Between #1/03/2004# And #31/05/2004#,"Autumn",IIf([DateAdded] Between #1/06/2004# And #31/08/2004#,"Winter",IIf([DateAdded] Between #1/09/2004# And #30/11/2004#,"Spring","Summer"))))

    The only problem I have is that, by default, Access, places 2004 after the date and month, even though in the original statement I only placed 1/09, or whatever. How can this be rectified so that for any year entered into the DateAdded field, the SeasonName field will recognise it and still classify the season accordingly (i.e. so that if I enter 6-Jun-1994, then it will still recognise 6 June as Winter, or 12-Dec-1994 as Summer). At the moment it's using the False clause to apply "Summer" to any date value that is not from 2004.

    Additionally, I need a validation rule in a table field - so that if a date prior to, say the 8 August 1996 is entered, then the Validation text box option will tell me I can't enter that value. Similarly, I need one to check that any ArrivalTime entered is between 8:00AM and 9:00PM, because they are the opening hours of the restaurant.

    Thanks.

    - Grace
    Last edited by graceadair; 05-15-04 at 00:24.

  2. #2
    Join Date
    Jul 2003
    Posts
    73

    That's quite an edit!

    Grace - I could swear I was responding to a completely different quetion. You must have edited your post (a lot) while I was typing my response. What I've written here may serve no purpose for you now - but I'll keep it here just incase. I'll have a look at your (semi) new question

    IMO opinion - you should store the season as you are (an integer on the database) - and then only translate it to a season in your forms. There's no use in storing "Autumn" in your database when 2 tells you the same thing.

    When you display the season (whether it be a label or text field), you'd obviously need to write something to translate it from the DateAdded.

    It could be done in the ControlSource property - which a bit of a long expression:

    IIF(DatePart("q", [DateAdded]) = 1, "Summer", IIF(DatePart("q", [DateAdded]) = 2, "Autumn", IIF(DatePart("q", [DateAdded]) = 3, "Winter", "Spring")))

    (Disclaimer - I never learned my seasons at school - so I've probably got the order wrong here :/ )

    Personally, I usually use unbound labels for this - and use the Form's OnCurrent event to change it each time the record is changed (next, previous etc)

    Code:
    Dim strSeason as String
    
      If IsNull(DateAdded.Value) Or Len(DateAdded.Value) = 0 Then
        SeasonLabel.Caption = ""
        Exit Sub
      End If
    
      Select Case DatePart("q", DateAdded.Value)
        Case 1:
          strSeason = "Summer"
        Case 2:
          strSeason = "Autumn"
        Case 3:
          strSeason = "Winter"
        Case 4:
          strSeason = "Spring"
      End Select
    
      SeasonLabel.Caption = strSeason
    Last edited by joeldixon66; 05-15-04 at 00:31.
    Joel Dixon
    Analyst Programmer
    Melbourne, Australia

  3. #3
    Join Date
    May 2004
    Posts
    80

    Lol...I was editing

    Yeah, I came up with a solution just 10 minutes after posting my original question - I was like "Arrrghhh, the poor people reading my posts!" I ask so many questions I must drive you all nuts. Oh, well. Thanks for replying, though. I did something fairly similar to what you did, but then I remembered that seasons go in the form of

    December - March : Summer
    March - June: Autumn
    June - September: Winter
    September - December: Spring

    And, lo and behold, the DatePart "q" value would only apply to January - April, April - July, July - October, and October - January!! So it's all mixed up. That's why I tested out the Between....And..... thing, which seemed to work (except not completely, as you'll see in my new post).

    But thanks heaps for your reply anyway, I love this messageboard, I get so many good answers and I'm actually learning heaps too.

    - Grace
    Last edited by graceadair; 05-15-04 at 00:40.

  4. #4
    Join Date
    Jul 2003
    Posts
    73
    Quote Originally Posted by graceadair
    Right, I used a nested IIF statement to determine the SeasonName from the DateAdded field, using Between....And......

    =IIf([DateAdded] Between #1/12/2004# And #31/12/2004#,"Summer",IIf([DateAdded] Between #1/03/2004# And #31/05/2004#,"Autumn",IIf([DateAdded] Between #1/06/2004# And #31/08/2004#,"Winter",IIf([DateAdded] Between #1/09/2004# And #30/11/2004#,"Spring","Summer"))))
    See my previous post - and check if that statement works for you.

    Quote Originally Posted by graceadair
    Additionally, I need a validation rule in a table field - so that if a date prior to, say the 8 August 1996 is entered, then the Validation text box option will tell me I can't enter that value. Similarly, I need one to check that any ArrivalTime entered is between 8:00AM and 9:00PM, because they are the opening hours of the restaurant.
    Do users enter everything with forms? If so - click on the DateAdded field - and look at the properties. There's a ValidationRule and ValidationMessage property (not sure on exact names - but that's how I remember them). In put:
    Code:
    > #8/8/1996#
    in the DateAdded ValidationRule property - and "Date must be greater than 8 August 1996" in the DateAdded ValidationMessage property.

    Also something like:
    Code:
    > #8:00#
    in the ArrivalTime rule. NOTE: I'm not 100% on how to specify a time in the validation rule field - but play around for a while and you should get it
    Joel Dixon
    Analyst Programmer
    Melbourne, Australia

  5. #5
    Join Date
    Jul 2003
    Posts
    73
    Quote Originally Posted by graceadair
    And, lo and behold, the DatePart "q" value would only apply to January - April, April - July, July - October, and October - January!! So it's all mixed up.
    If this still isn't working - you could use month of the Date (DatePart("m", DateAdded)) to check as follows:

    Code:
    Select Case DatePart("m", DateAdded)
      Case 12 Or 1 Or 2:
        strSeason = "Summer"
      ' etc
    End Select
    (Once again - no knowledge of the seasons (I also take a few seconds deciding which is left and which is right ) - and I'm in Australia - so mine are backwards to yours!)
    Joel Dixon
    Analyst Programmer
    Melbourne, Australia

  6. #6
    Join Date
    May 2004
    Posts
    80

    Thanks

    Thanks again for your reply...your suggestion, while great, won't work because of the overlapping season issue - and then, the Between...And.... statement thing doesn't quite work because of the year issue - so would it be plausible, do you think, to have a separate field for year (and leave that part out of the whole Season equation altogether) and just calculate the season from DateAdded using the dd/mmmm Date/Time property?

  7. #7
    Join Date
    May 2004
    Posts
    80

    Ooh I like your new suggestion

    Now that is CLEVER. I must try that. Hehe. So I use the Code Builder for this one??

  8. #8
    Join Date
    May 2004
    Posts
    80

    Lol I'm Aussie too

    I'm an Aussie as well....must confuse all the Northern Hemispherians....

  9. #9
    Join Date
    Jul 2003
    Posts
    73
    P.S. - Sorry for the late reply - my computer just decided to have a fit

    Quote Originally Posted by graceadair
    So I use the Code Builder for this one??
    Yeah - if you're displaying the season in a form - I'd put the following in the Form's OnCurrent event (Code Builder):

    Code:
    Private Sub Form_OnCurrent()
    
    Dim strSeason as String
    
      If IsNull(DateAdded.Value) Or Len(DateAdded.Value) = 0 Then
        SeasonLabel.Caption = ""
        Exit Sub
      End If
    
      Select Case DatePart("m", DateAdded.Value)
        Case 12 Or 1 Or 2:
          strSeason = "Summer"
        Case 3 Or 4 Or 5:
          strSeason = "Autumn"
        Case 6 Or 7 Or 8:
          strSeason = "Winter"
        Case 9 Or 10 Or 11:
          strSeason = "Spring"
      End Select
    
      SeasonLabel.Caption = strSeason
    
    End Sub
    Quote Originally Posted by graceadair
    I'm an Aussie as well....must confuse all the Northern Hemispherians....
    I guess I should have figured you were an Aussie - as there isn't much activity from the Americans ATM. It's funny - I just assumed you were in the US - but we could possibly be in the same Suburb (though I doubt it!)!

    Good luck - let me know if the above code works
    Joel Dixon
    Analyst Programmer
    Melbourne, Australia

  10. #10
    Join Date
    May 2004
    Posts
    80

    Arrrrghhh can't find OnCurrent!!

    I've looked up the list under the field Properties in Design view, and I can't find the OnCurrent box, even though I've seen it before!! Is there any other event it could go under, or can you offer a suggestion as to why Access is playing tricks on me?!

    Also, would it bother you if I sent you emails with questions? Hope that's OK.

    Thanks again, so much.

  11. #11
    Join Date
    Jul 2003
    Posts
    73
    The OnCurrent event is actually on the Form itself. You use it for anytime the record in the form is changed. Select the form and you'll see the entry (I think it's Ctrl+R to Select Form).

    But be prepared for Access to play tricks on you as well!! It does that all the time!

    Quote Originally Posted by graceadair
    Also, would it bother you if I sent you emails with questions? Hope that's OK.
    Sure - send emails if you'd like - but the Yahoo one I have in my signature isn't the one I check from work. I only get around to checking it every few days. But email if you'd prefer - I really should check that account more often
    Joel Dixon
    Analyst Programmer
    Melbourne, Australia

  12. #12
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Since I am also Australian and the yanks are still in bed can either of you solve a problem for me. The diversion will help you with summer and winter seasons

    I have the following attached to OnClick on differenet labels to open a Word doc and then paste in the contents of lots of Access fields to Word bookmarks. Is there way to the same thing for Excel and paste to Cells

    Private Sub Label118_Click()

    Const MSTB_MSWORD = 300&

    Application.Run "utility.util_StartMSToolbarApp", MSTB_MSWORD

    Dim docName As Object
    Set docName = CreateObject("Word.Basic")

    docName.FileOpen "c:\Letters\SoA.doc"

    DoCmd.RunMacro "Macro11Street", , ""
    docName.EditGoTo "a3"
    docName.EditPaste

    Mike

  13. #13
    Join Date
    Jul 2003
    Posts
    73
    Hey Mike - what makes you think we need a diversion from Summer and Winter

    For your problem - it depends why you want to dump to Excel. There's a few options: If you just want to dump all of the fields to a NEW Excel file - there's a way you can actually export a query to a new Excel file.

    If you want to edit an existing Excel file with your Access data - you can actually create a link table to the Excel file. This way you can maintain the Excel file from within Access code - and it's a lot easier!

    In terms of actually opening up the Excel file in Excel from Access - I'm not sure. But one of the above options may help you a bit. Ages ago I needed some Access data to update Excel spreadsheets - and I wrote code in Access that dumped the relevant data to a CSV file, then wrote some VBA behind an Excel file to open the CSV file and update it's own information. Something like that might help.

  14. #14
    Join Date
    May 2004
    Posts
    80

    Unhappy Errrrr...not a clue

    Sorry, I have absolutely no idea...I'm not very good at this sort of in-depth stuff.

    I could try just for the sake of trying?? Here we go. This'll be good. Maybe you could change it to:

    Private Sub Label118_Click()

    Const MSTB_MSEXCEL = 300&

    Application.Run "utility.util_StartMSToolbarApp", MSTB_MSEXCEL

    Dim docName As Object
    Set docName = CreateObject("Excel.Basic")

    docName.FileOpen "c:\Letters\whatever.xls"

    DoCmd.RunMacro "Macro11Street", , ""
    docName.EditGoTo "a3"
    docName.EditPaste

    Would that do the job??? Hope so.

    Will post another message for you on the same thread in a second because I need to ask another question (for either of you guys) if that's OK with you.

  15. #15
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    I can open Excel Ok. You just use Access to make a command button and select from their built in options and then of course copy the one to open Excel. But I fiddled about trying to open a specific xls file etc and went to pieces. I have no real reason to do it. A similar subject came up in a another thread and I thought if I could do what I do in Word with Excel it might come in handy one day. Alternative I could entertain myself and spray the contents of Access fields all over a spreadsheet. I will try graceadair's and see how that goes.

    This is off the Access comand button in Access 95

    Sub Command0_Click()
    On Error GoTo Err_Command0_Click

    Const MSTB_MSEXCEL = 310&

    Application.Run "utility.util_StartMSToolbarApp", MSTB_MSEXCEL

    Exit_Command0_Click:
    Exit Sub

    Err_Command0_Click:
    MsgBox Err.Description
    Resume Exit_Command0_Click

    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •