Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2009
    Posts
    6

    Question Unanswered: Moving over to code and choosing a date

    Hi Everyone,

    Following advice on here I have decided to ditch Macros and move over to learning VBA. This is where I get stuck.

    I have a table which contains the following:

    tbl_daily_gifts {date, gift code, issued_today, max_today}

    When a user clicks on a button in a form I want to validate the gift is available on that date so the pseudocode kind of looks like this..

    Read today's date
    Is that gift available today?
    If No = MsgBox "That gift isn't available"; End
    If Yes = max_today-1 ; issued_today+1
    Continue with rest of procedure


    The rest of the procedure after this works. I just want to add this above in as extra validation or else guests will be able to claim gifts and print vouchers even if they are not available on that day.

    The table (tbl_daiy_gifts) has several gifts in there available on numerous days. I just don't know how to get the code to compare todays date to the one in the table.

    Any help appreciated,

    Gareth

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Hey Gareth,
    Quote Originally Posted by welsh_gje
    Read today's date
    Do a help search on the Date() function, and you may also want to look at the Now() function.
    Quote Originally Posted by welsh_gje
    Is that gift available today?
    What kind of logic goes into deciding this?
    Me.Geek = True

  3. #3
    Join Date
    Jan 2009
    Posts
    6
    Quote Originally Posted by nckdryr
    Hey Gareth,

    Do a help search on the Date() function, and you may also want to look at the Now() function.

    What kind of logic goes into deciding this?
    Hi,

    Thanks for the reply. The logic? Well I'll try to explain why this is happening. The Hotel Manager chooses which gifts are available on which day. There are six gifts (G1, G2, G3 and so on). The manager may have 5 x G1 on the 29/01/2009 but 0 x G1 on 30/01/2009 - therefore I was looking to get the code to see if there were any gifts available on that date.

    If the answer was true then the code would check to see if the number was above 0. If so then the code would then check to see if the hotel guest had enough points to claim that gift. If so it would print the gift voucher (and decrease the number of G1's available by 1, and remove the points from the guest).

    So far I have got to the point where the code checks how many points the guest has and if there is enough removes them, puts the new total back into the field and prints out the voucher.

    I want one step before this - that is whether the gift is available on the day before even getting to checking the guest points.

    I have the logic out in my head with the If...Then...Else - just not how to write it in the VBA code (the check date bits).

    Again thanks for your time looking a this,

    Gareth

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    Well, if I understand your question correctly, it sounds like something like this might work for you:

    Code:
    'Instantiate Variables
    Dim blnGiftAvailable As Boolean, _
        blnEnoughPoints As Boolean
    Dim intAvailable As Long, _
        intIssued As Long
    
    'Find if gift is available today
    intAvailable = Nz(DLookup("max_today", _
                                "tbl_daily_gifts", _
                                "[fldDate] = #" & Date & "#" _
                                    & " AND " & _
                                    "[gift code] = '" & GCodeChosen & "'"), _
                        -1)
    intIssued = Nz(DLookup("issued_today", _
                                "tbl_daily_gifts", _
                                "[fldDate] = #" & Date & "#" _
                                    & " AND " & _
                                    "[gift code] = '" & GCodeChosen & "'"), _
                        -1)
    If (intIssued < intAvailable) And (intIssued >= 0) And (intAvailable >= 0) Then
        blnGiftAvailable = True
    Else
        blnGiftAvailable = False
    End If
    
    'Find if User has enough points
    'You should have this part from the last thread ;)
    
    'Respond based on criteria
    If blnGiftAvailable And blnEnoughPoints Then
        'update value and issue coupon
    Else
        MsgBox "That gift is not available for you right now.", _
                    vbCritical, _
                    "Invalid Selection"
    End If
    Now it's up to you to decipher this and figure it out for yourself.

    By the way, I would STRONGLY recommend you change the name of the "date" field; that's one of those Access reserved words that will get you into trouble later if you use it as a fieldname. At the very least, name it "fldDate", though I would suggest a more descriptive field name.

    Also, I'm assuming your Gift Code field is a string type. If it's a number type, you can remove the apostrophes around the dlookup() criteria string. Similarly, I'm assuming the "date" field is a Date/Time format, hence the pound signs around the Now() date.

    And at the risk of making you think about one thing, it can be frustrating from a User's stand point to click an option that you are given only to get a message box saying you can't have that. It might be better if you not offer those choices in the first place. But this may be a bit more than you'd like to do right now.

    Let us know how you get along, and as always, post with what you have so far so we can help.
    Me.Geek = True

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by nckdryr
    Hey Gareth,

    Do a help search on the Date() function, and you may also want to look at the Now() function.

    What kind of logic goes into deciding this?

    be very carefull of which date/time function you choose
    access/JET stores dates and times in the same decimal datatype.
    the integet part indicates the days elapsed sine a point in time (IIRC 01/01/1900), the decimal part indicates the time element. eg 1 hour is stored as 1/24
    1 minute is 1/( 60*24)

    date() returns the current date, with the time element set to 0 ie 00:00:00
    now() returns the date AND time.
    so when ever dealing with date/time values make certain in your mind you understand what the data is saved as

    ferisntance
    if you use now() to store a date and say it 10:53:45 on 02 Jan 2009
    when you set your where clause you need to make sure to take account of the time element.
    in some cases it may make more sense, especially if the app is for use by no systems people for querying to separate out the time element.

    eg
    where mydate <= 02 Jan 2009 .. this row will not be returned as the time element is greater than 00:00:00.
    the SQL engine is not smart eough to think you are only looking at dates.

    so you either have to add the time element sa part fo he where clause or go slightly cruder and add on day to your upper date band
    eg
    where mydate < 03 jan 2009

    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jan 2009
    Posts
    6
    Hi nckdryr

    I've been playing with this - and have probably butchered it along the way in my quest to learn.

    Code:
    Function mcr_chosen_gift_one()
    
    'Instantiate Variables
    Dim blnGiftAvailable As Boolean, _
        blnEnoughPoints As Boolean
    Dim intAvailable As Long, _
        intIssued As Long
    
    'Find if gift is available today
    intAvailable = Nz(DLookup("Max_Today", _
                                "tbl_rhodd_y_dydd", _
                                "Dyddiad = #" & Date & "#" _
                                    & " AND " & _
                                    "Gift_Code = '" & GCodeChosen & "'"), _
                        -1)
    intIssued = Nz(DLookup("Issued_Today", _
                                "tbl_rhodd_y_dydd", _
                                "Dyddiad = #" & Date & "#" _
                                    & " AND " & _
                                    "Gift_Code = '" & GCodeChosen & "'"), _
                        -1)
    If (intIssued < intAvailable) And (intIssued >= 0) And (intAvailable >= 0) Then
        blnGiftAvailable = True
    Else
        blnGiftAvailable = False
    End If
    
    'Quick check to see the content of the variable - is the gift available?
    MsgBox "First variable check point"
    MsgBox "blnGiftAvailable = " & blnGiftAvailable
    
    If blnGiftAvailable = True Then
    If (Forms!frm_qry_choose_gift![Point Balance] < 150) Then
            Beep
            MsgBox "You do not have enough points. Sorry", vbOKOnly, ""
            blnEnoughPoints = False
            Exit Function
    End If
    
            If (Forms!frm_qry_choose_gift![Point Balance] >= 150) Then
              DoCmd.GoToControl "Point Balance"
              Forms!frm_qry_choose_gift![Point Balance] = Forms!frm_qry_choose_gift![Point Balance] - 150
              blnEnoughPoints = True
            End If
    End If
    
    'Quick check to see the content of the variables
    MsgBox "blnGiftAvailable = " & blnGiftAvailable
    MsgBox "blnEnoughPoints = " & blnEnoughPoints
    
    'Respond based on criteria
    If blnGiftAvailable And blnEnoughPoints Then
        'print coupon
        MsgBox "Printing Coupon"
        DoCmd.OpenReport "rpt_gift_one_voucher", acViewReport, "", "", acNormal
    Else
        MsgBox "That gift is not available for you right now.", _
                    vbCritical, _
                    "Invalid Selection"
    End If
    
    
    End Function
    So as you can see I have put MsgBoxes in at certain points to show me the content of the variables. I have put the relevant data in the tbl_rhodd_y_dydd and the correct date (today 01/02/2009) - but the variable is still coming back as False.

    I have probably messed with the code a little too much. Thanks for your help,

    Gareth

  7. #7
    Join Date
    May 2005
    Posts
    1,191
    My apologies, I should have pointed out the lines
    Code:
    "Gift_Code = '" & GCodeChosen & "'"
    will have to be adapted to however you are supplying this information. For instance, if the user picks one from a combo box named "cboGiftCode", then you would change this to be
    Code:
    "Gift_Code = '" & me.cboGiftCode & "'"
    See if that helps.

    I would strongly recommend that you do a help search on all the functions used to make sure that you understand what this code is doing. I would probably also suggest you turn on the debugger and see what it's doing line-by-line if you really want to understand it. I'm just trying to offer friendly advice, I'm not guaranteeing this will work for all cases ever; that's your job
    Me.Geek = True

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by nckdryr
    I would strongly recommend that you do a help search on all the functions used to make sure that you understand what this code is doing. I would probably also suggest you turn on the debugger and see what it's doing line-by-line if you really want to understand it. I'm just trying to offer friendly advice, I'm not guaranteeing this will work for all cases ever; that's your job
    AMEN to that
    this forum is voluntary, people make suggestions on how you as the developer could do things.
    some of the suggestions work, some don't, some may be appropriate to your solution, some may not. some of the suggestions are skeletons, ie this is how you could do it. most contributors do not have enough time in their real jobs to provide full working solutions.
    besides which you will learn more from doing it yourself rather than picking up someoneelses code. rarelty is supplied code fully commentated, and it ALWAYS pays to review the code line by line to make sure you understand what its doing, espcecially some of the odder functions which old hacks know off by heart
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Jan 2009
    Posts
    6
    Quote Originally Posted by healdem
    AMEN to that
    this forum is voluntary, people make suggestions on how you as the developer could do things.
    some of the suggestions work, some don't, some may be appropriate to your solution, some may not. some of the suggestions are skeletons, ie this is how you could do it. most contributors do not have enough time in their real jobs to provide full working solutions.
    besides which you will learn more from doing it yourself rather than picking up someoneelses code. rarelty is supplied code fully commentated, and it ALWAYS pays to review the code line by line to make sure you understand what its doing, espcecially some of the odder functions which old hacks know off by heart
    Oh I agree totally. The effort is appreciated. I don't agree that doing it yourself always leads to the best results - I have to admit I learn better reading people's code and seeing their ideas - especially in the first instance. Of course it would be easy just to steal the code and not do anything with it - but I don't see how I am ever going to learn like that.

    There are different types of learners based on Edward De Bono's Coloured Thinking Hats. I am a look and learn person.

    Best wishes - and thanks once again for the help.


    Gareth
    Last edited by welsh_gje; 02-02-09 at 17:34.

Posting Permissions

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