Page 1 of 3 123 LastLast
Results 1 to 15 of 41
  1. #1
    Join Date
    Jan 2004
    Location
    OHIO
    Posts
    75

    Unanswered: vba code - getting an errror

    Below is the code I'm working with. I'm filtering a form with a dropdown and a listbox. The error I'm getting with this code is stated below the code section.

    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub cmdGo_Click()
    On Error GoTo Error_Handler
    
    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim varItem As Variant
    Dim strCodes As String
    
        ' ensure that a season has been selected
        If IsNull(Me.cboSeason) Then
            MsgBox "Please select a Season.", vbInformation + vbOKOnly, "Season Required"
            Me.cboSeason.SetFocus
            GoTo Normal_Exit
        End If
        
        ' ensure that at least one code has been selected
        If lstCode.ItemsSelected.Count = 0 Then
            MsgBox "Please select one or more codes.", vbInformation + vbOKOnly, "Code(s) Required"
            Me.lstCode.SetFocus
            GoTo Normal_Exit
        End If
        
        ' obtain list of selected sports
        With Me!lstCode
            For Each varItem In .ItemsSelected
                strCodes = strCodes & " Or [AwardName] = " & "'" & .Column(0, varItem) & "'"
            Next varItem
        End With
        
        stDocName = "rptAwards"
        
        strCodes = Right(strCodes, (Len(strCodes) - 12))
        'MsgBox strCodes
    
        stLinkCriteria = "[DateReceived]=" & "'" & Me![cboSeason] & "' And ([AwardName] = " & strCodes & ")"
        'MsgBox stLinkCriteria
    
        DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
        
    Normal_Exit:
        Exit Sub
        
    Error_Handler:
        MsgBox Err.Number & ": " & Err.Description
        Resume Normal_Exit
        
    End Sub
    I have attached a sample db of so you can see first hand what I am tripping over. The error message is

    3075: Syntax error in query expression . . .

    In the sample db, check out the form "Awards - PromptBox"

    Other important tables, queries.... "qryAwards"

    Appreciate your help!
    Last edited by ironchef; 01-19-04 at 16:28.

  2. #2
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: vba code - getting an errror

    There seems to be a problem with the sample db you've posted... I get an error when I try to open the zip file... Could you check it out and try posting it again??

    Have you stepped through the code to see which line is giving you this error message?

    Without looking at the db itself, I can see a couple of things that could be causing you trouble...

    1)
    For Each varItem In .ItemsSelected
    strCodes = strCodes & " Or [AwardName] = " & "'" & .Column(0, varItem) & "'"
    Next varItem

    strCodes = Right(strCodes, (Len(strCodes) - 12))


    I'm assuming you're using this code to create the where clause... and taking the data on the Right to drop off the first " Or [AwardName = "... Well that string is 18 characters long... The -12 is not long enough... As it is now, I believe your strCodes will end up starting with "me] = "... Try making it ...
    strCodes = Right(strCodes, (Len(strCodes) - 18))

    2)
    stLinkCriteria = "[DateReceived]=" & "'" & Me![cboSeason] & "' And ([AwardName] = " & strCodes & ")"

    Are you sure that you're reflecting the correct data types in this criteria?... Logic would dictate that [DateReceived] would be a Date type field... Therefore, the comparison data should be surrounded with # signs...If I'm correct in my assumptions, your code should look like this...

    stLinkCriteria = "[DateReceived]=#" & Me![cboSeason] & "# And ([AwardName] = " & strCodes & ")"

    HTH

  3. #3
    Join Date
    Jan 2004
    Location
    OHIO
    Posts
    75
    it seems when i compact and repair the database and zip it, it becomes corrupt . . . ? can i try emailing to you?

    also, I tried implementing the ideas that you had and they same error would pop up....

    thanks
    Last edited by ironchef; 01-19-04 at 16:58.

  4. #4
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Originally posted by ironchef
    it seems when i compact and repair the database and zip it, it becomes corrupt . . . ? can i try emailing to you?

    also, I tried implementing the ideas that you had and they same error would pop up....

    thanks
    That should be fine... How big is the .mdb file after it's compacted?

  5. #5
    Join Date
    Jan 2004
    Location
    OHIO
    Posts
    75

    database attached

    try this... if this doesn't work I'll email it too you.


    and it says its a .mdb file after compacted
    Attached Files Attached Files
    Last edited by ironchef; 01-19-04 at 17:23.

  6. #6
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: database attached

    Originally posted by ironchef
    try this... if this doesn't work I'll email it too you.


    and it says its a .mdb file after compacted

    Thanks! ...It worked...
    and I asked how big the file was, not what it was... lol...

    Here you go...
    I made two changes to your code...

    1) strAwardName = Right(strAwardName, (Len(strAwardName) - 12))
    was changed to...

    strAwardName = Right(strAwardName, (Len(strAwardName) - 18))

    Like I said before, you the value of strAwardName was still including "me] = " when you only took from 12 characters into the string...

    2) The data in your YearList control is a year only... So I looked at the data in DateReceived in qryAwards and it was full dates... ex. 12/24/03... So I added the Year() function around the DateReceived data and bingo... it opened the report... no problem... See the line of code below...

    stLinkCriteria = "Year([DateReceived])=" & "'" & Me![YearList] & "' And ([AwardName] = " & strAwardName & ")"

    Make those two changes and you will be fine...

    One other thing... If you don't know how to step through code, you really should learn how to set a break point and step it through... That way you'll be able to see the value held in your variables as you go through the procedure... Debugging is a skill that everyone who codes needs to learn...

    I can type you up instructions if you'd like?... or you can find it in Access VBA Help...

    HTH ...Have a great night!

  7. #7
    Join Date
    Jan 2004
    Location
    OHIO
    Posts
    75

    alrighty

    ok, got that to work sometimes... for example i need it to be able to show multiple selections in the list box, for instance...pick Date of 2003 and then select all of the awards and run the report. You'll see that i am still getting the error....what i was thinking was if a user picks an award that and a certain year that no one received an award then either a message appears on the report like no records found or better than that a prompt box says sorry no records for that year and award(s).

    Now, if someone picks like 2003 and all of the awards and some of the awards do not fall under the criteria then either it says something on the report like "none" or it just doesn't print the ones that are not in there.

    i appreciate you helping me, as you have already figured i'm an amature access developer. i really do appreciate your help! I'm going to be up late working on this project, and i will keep my eye on the forum.

    Thanks a ton!

  8. #8
    Join Date
    Jan 2004
    Location
    OHIO
    Posts
    75

    alright i figured out how to manipulate the error message

    i figured out how to manipulate the error message (what it says to tyhe user) however my problem is that if i pick 2003 for the year and highlight all the awards it gives me the error message, however i know that the statement is partially true that some of the awards were received in 2003. i need to be able to only report those awards won.....how can this be achieved?

    thanks in advanced.

  9. #9
    Join Date
    Jan 2004
    Location
    OHIO
    Posts
    75

    stepping through code

    Trudi,

    If it's not to much trouble, I would appreciate some instructions of walking through code and debugging. I'm a amature coder and I am eager to learn more about coding and the techniques that improve my skills.

    One thing I was working on today is testing the code and queries for the Awards - Prompt Box and making sure all the options are working correctly. However, I found that if you select 2004 and the award "President's Award" that the query gets an error. I have looked at the main query that gets the information of dates/years and the awards and there is a record of that meets the criteria of 2004 and "President's Award". I'm guessing that it has to do with something in this piece of code:

    Code:
    strAwardName = Right(strAwardName, (Len(strAwardName) - 18))
    After some research, it looks like this line of code does this (how i understand it, correct me if i'm wrong).

    It figures out the strAwardName from right to left, then takes the length of that and subtracts 18.... I'm not sure why though and I assuming that this has something to do with the problem i talked about in this post.
    Last edited by ironchef; 01-20-04 at 11:14.

  10. #10
    Join Date
    Jan 2004
    Location
    OHIO
    Posts
    75

    any ideas?

    any ideas... i having the same problem with another form filter exactly like the one here i have posted questions about... some help would be great!

    thanks

  11. #11
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: alright i figured out how to manipulate the error message

    Originally posted by ironchef
    i figured out how to manipulate the error message (what it says to tyhe user) however my problem is that if i pick 2003 for the year and highlight all the awards it gives me the error message, however i know that the statement is partially true that some of the awards were received in 2003. i need to be able to only report those awards won.....how can this be achieved?

    thanks in advanced.
    Hi there...

    Sorry I didn't get back to you yesterday... It was one of THOSE weeks... lol...

    k... I don't have your database here at work... (can't receive attachments here) ... so I may have to work on this tonight... but let's see what we can do...

    If you pick all of the award types in the list box, you get an error message?... The same one saying that the query syntax is wrong?
    Hmmm... That shouldn't happen... You're using the OR operator in your Where clause... We'll have to step through the code and see the values in your variables... (I'll explain how...)

    Question... When you say "those awards won", is this something different than we were dealing with before?... I mean do you know that the award was won just by finding a record for it in the query?... or is there another field designating a win?

  12. #12
    Join Date
    Jan 2004
    Location
    OHIO
    Posts
    75

    i think i might of figured it out

    alright....

    I figured out the error, which it was right under our noses! One of the awards is:

    "President's Award" --- Look closely at the award..... you will see it has an apostrophe. Which creates havic in our sql statement, producing the syntax error...lol

    Thank you so much for your help! I really appreciate it. My next problem is making a filter form that prompts for begin and end date and a listbox of possible jobs. I have done both of these filter seperately, however not together. Hopefully I can figure it out. I posted the two pieces of code that i have below if you have any suggestions, I appreciate it!

    Thanks again,

    Brad


    Code:
    Private Sub Preview_Click()
        If IsNull([BeginDate]) Or IsNull([EndDate]) Then
            MsgBox "You must enter both beginning and ending dates."
            DoCmd.GoToControl "BeginDate"
        Else
            If [BeginDate] > [EndDate] Then
                MsgBox "Ending date must be greater than Beginning date."
                DoCmd.GoToControl "BeginDate"
            Else
                Me.Visible = False
            End If
        End If
    End Sub
    query code
    Code:
    >=[forms]![Report Date Range]![BeginDate] And <=[forms]![Report Date Range]![EndDate]
    Then the listbox

    Code:
    Private Function GetCriteria() As String
        Dim stDocCriteria As String
        Dim VarItm As Variant
            For Each VarItm In ListFilter.ItemsSelected
            stDocCriteria = stDocCriteria & "[JobID]= " & ListFilter.Column(0, VarItm) & " OR "
        Next
            If stDocCriteria  "" Then
            stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 4)
        Else
            stDocCriteria = "True"
        End If
        
        GetCriteria = stDocCriteria
    End Function
    
    Private Sub Command27_Click()
        DoCmd.OpenReport "rptJobABT", acPreview, , GetCriteria()
    End Sub

    Not sure if you have dealt with this before but any information would help.

    Thanks!

  13. #13
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: stepping through code

    First things first... I'm going to write up debugging instructions... so I won't get into that at this time...

    As for the "President's Award" problem.... I believe that you're getting the error because of the apostrophe... (I'll tell you why in a minute...)

    I'm assuming that you've copied this procedure code from an example somewhere so let me try to explain... What you are doing in this procedure is "concatenating" a string to create the criteria with which to decide what records to show when you open the report... You are building this final statement...
    Code:
    DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
    If you look up OpenReport Action in Access Help you'll get information on this, but I'll try explain...

    stLinkCriteria is your "Where Condition"... In SQL terms, it means that you want to select/insert/delete records WHERE some field(s) equal/don't equal/look like/etc some other value(s)... So let's work backwards through your code...
    Code:
    stLinkCriteria = "[DateReceived]=" & "'" & Me![cboSeason] & "' And ([AwardName] = " & strCodes & ")"
    Say the user chooses 2003 and Star Award... The final string should be...
    Code:
    stLinkCriteria = "[DateReceived] = '2003' And [AwardName] = 'Star Award'"
    Notice that this is the first time [DateReceived] is introduced... That is because the user can only select one date at a time... However, they CAN choose more than one [AwardName]... This means we have to build a string for all of the user's choices... 'Star Award' OR 'President Award' OR ... etc... The most efficient way of building this string is to Loop through the choices... Your code below is the loop...
    Code:
    ' obtain list of selected sports
        With Me!lstCode
            For Each varItem In .ItemsSelected
                strCodes = strCodes & " Or [AwardName] = " & "'" & .Column(0, varItem) & "'"
            Next varItem
        End With
    Okay... So let's say the user chooses only one award again... The Star Award... The first time through this FOR/NEXT loop, the value in strCodes is...
    Code:
    strCodes = " Or [AwardName] = 'Star Award'"
    Now let's say they chose two Awards... The second time through the FOR/NEXT loop... strCodes' value is then...
    Code:
    strCodes = " Or [AwardName] = 'Star Award' Or [AwardName] = 'President Award'"
    Therefore... Without the...
    Code:
    strCodes = Right(strCodes, (Len(strCodes) - 18))
    line of code... when it gets to the bottom of the procedure...
    Code:
    stLinkCriteria = "[DateReceived]=" & "'" & Me![cboSeason] & "' And ([AwardName] = " & strCodes & ")"
    
    stLinkCriteria = "[DateReceived] = '2003' And [AwardName] = Or [AwardName] = 'Star Award' Or [AwardName] = 'President Award'"
    Think that makes sense as a Where clause?... Nope...
    So what do we know?... We want at least one AwardName choice... maybe more...
    Code:
    strCodes = Right(strCodes, (Len(strCodes) - 18))
    This line of code takes the final strCodes value and takes the first " Or [AwardName] = " off the front of the string... Count the characters in that phrase.... 18... So we need to get rid of the first occurence of that phrase...

    The code above interprets to this... Say the length of strCodes is 100 characters ... ie ... Len(strCodes) = 100 ... We want the Right side of the string... 100 characters minus the 18 for the first " Or [AwardName] = " ... ie 82 characters...

    Make sense now?

    Now... The problem with your "President's Award" option is that there is an apostrophe... An apostrophe is interpretted in this VBA statement as the start or end of a string value in this Where clause... so you're trying to say...
    Code:
    strCodes = strCodes & " Or [AwardName] = " & "'" & .Column(0, varItem) & "'"
    
    strCodes = " Or [AwardName] = 'President's Award'"
    This is where the error occurs... The compiler says what's this stuff after 'President'??... There's an apostrophe so that must be the end of the string value? ... Hmmmm...

    Get it?

    Now... to fix it... Well there are ways... The easiest would be for you to remove the apostrophe in the name ... just call it "Presidents Award" ... If that won't do, we can talk about adding an IF statement in your code that will fix this problem when it arises...

    Let me know...

    (Mannnnn can I go on and on huh?? lol... I hope I haven't gotten you more confused with all this... lol)

  14. #14
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: i think i might of figured it out

    Oops... I was so busy rambling that you'd already solved the problem with the apostrophe... lol... It's really difficult to recognize someone's level of knowledge on here... I thought I was helping... Sorry about that... LOL...

    As for the filter form you mentioned... Your Preview code looks fine... Your query criteria looks fine... The code below looks like it has a problem or two...
    Code:
    Private Function GetCriteria() As String
        Dim stDocCriteria As String
        Dim VarItm As Variant
            For Each VarItm In ListFilter.ItemsSelected
            stDocCriteria = stDocCriteria & "[JobID]= " & ListFilter.Column(0, VarItm) & " OR "
        Next
            If stDocCriteria  "" Then
            stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 4)
        Else
            stDocCriteria = "True"
        End If
        
        GetCriteria = stDocCriteria
    End Function
    
    Private Sub Command27_Click()
        DoCmd.OpenReport "rptJobABT", acPreview, , GetCriteria()
    End Sub
    stDocCriteria is always going to turn out wrong...

    First you're doing a For/Next loop to create the string of JobIDs... Then you are doing an IF statement...

    If strDocCriteria is an empty string... Take the Left side of the string (all but 4 characters on the right) ... Ummm... If it's an empty string what do you want the string value to be?

    Else... (strDocCriteria is not an empty string)... Make strDocCriteria's value "True"... You are overwriting the string you've just built with the word True... I'm seriously doubting that's what you want??

    strDocCriteria and GetCriteria will always be either "" or "True"...

  15. #15
    Join Date
    Jan 2004
    Location
    OHIO
    Posts
    75

    Re: stepping through code


    (Mannnnn can I go on and on huh?? lol... I hope I haven't gotten you more confused with all this... lol)
    Trudi,

    Your explanation really help me understand the process of the code. I appreciate your time. I have to say, although you think you are long winded---i disagree, there are only a few members of forums that really take the time to explain things like this to amatures like me.

    The apostrophe problem: I think it would be safe to say that my users could use an apostrophe when adding new awards to the system. Therefore, using a "If Statement" might be a solution that saves my users the confusion and eliminate any loop holes. So, what do you recommend?

    Regarding my prior post about the filter form by begin and end date and job listbox....

    I'm not sure how to take the two pieces of code and make them work together.....if that makes sense.

Posting Permissions

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