Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Richie27 is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Location
    Ireland
    Posts
    159

    If statement syntax help!

    Hi all,

    I'm a bit unsure on the syntax for this - any suggestions would be wonderful! I am taking a number from the txt box and want to check if it is in queryresult, not sure how though. Cheers. queryresult will only return one field and one value.

    If [Forms]![frmFN_MainMenu]![txtBox] <> queryresult Then

    DoThis

    Else

    DoThat

    End If

    Hmmm, I'm thinking I need to assign queryresult to a variable and pop the variable in where 'queryresult' is. Again though, dunno how.......



    Rich.

  2. #2
    R_Badger is offline Knows a few tricks
    Windows XP Access 2003
    Join Date
    Feb 2012
    Location
    Suffolk, UK
    Posts
    262
    I had a look at your project located over at
    https://www.accessforums.net/attachm...3&d=1339665477

    I admit i had a hard job looking at it to figure out what you were trying to do.

    try something along the lines of

    Code:
    dim queryresult as variant
    
    queryresult = dmax("[boxno]","[table]","[where clause the same as your query]")
    
    if isnull(queryresult)=false then
    
    dothis
    
    else
    
    dothat
    endif

  3. #3
    Richie27 is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Location
    Ireland
    Posts
    159
    Absolutely Wonderful!!!!!!! Another useful tool to add to my arsenal.

    Thanks again R Badger!

    It's easy when you know how I guess!

    Didn't even know you could use DMAX like that.

  4. #4
    Richie27 is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Location
    Ireland
    Posts
    159
    Quick question, which I did not foresee.

    Can you similarly have a section of a report which chooses to use one query or another in a similar fashion to what you've just enlightened me on?

    Scratch that - I just figured out the answer to that - just create two reports - duh!

    Thanks R Badger

  5. #5
    R_Badger is offline Knows a few tricks
    Windows XP Access 2003
    Join Date
    Feb 2012
    Location
    Suffolk, UK
    Posts
    262
    You don't necessarily have to create two reports. If the structure and fields etc are the same, you can dynamically change the recordsource of the report.

    Fistrly create a text box [txt_source]

    then on the openreport button add the lines

    Code:
    dim queryresult as variant  queryresult = dmax("[boxno]","[table]","[where clause the same as your query]")  if isnull(queryresult)=false then  dothis [forms]![form]![txt_source]="Source1" else  dothat
    [forms]![form]![txt_source]="Source2"
    endif
    Then on the report open event

    Code:
    if [forms]![form]![txt_source]="source1" then
    me.recordsource= "SQL1"
    else
    me.recordsource="SQL2"
    endif
    Last edited by R_Badger; 06-14-2012 at 07:32 AM. Reason: Corrected the codde in the form section of the post

  6. #6
    Richie27 is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Location
    Ireland
    Posts
    159
    Gosh darn - I want to do it your way!! It may take a little morel jiggling but it's definitely better!

    How long have you been coding with VBA anyway?

    I really like it but it's great to have pointers when you hit a wall!

    Very novice but every little bit you learn you can use in so many different ways, it's great.

  7. #7
    Richie27 is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Location
    Ireland
    Posts
    159
    Would txtSource be an additional text box to what I have already? I have added this and made it invisible.

    I have also literally typed in source1

    In the report open event I have

    Private Sub Report_Open(Cancel As Integer)
    If [Forms]![frmFN_MainMenu]![txtSource] = "source1" Then
    Me.RecordSource = "qryFN_Rep_FinalAction_EXT"
    Else
    Me.RecordSource = "qryFN_Rep_FinalAction_AW"
    End If


    End Sub

    Nothing happens though....hmmmm......

  8. #8
    R_Badger is offline Knows a few tricks
    Windows XP Access 2003
    Join Date
    Feb 2012
    Location
    Suffolk, UK
    Posts
    262
    Its actually hard to say how long, I started my main current project (I have about 5 at the moment) in November and I think I started using VB in earnest in January or thereabouts.

    Its been a struggle, I made a lot of mistakes in my original data model and design concept (some of which are still present because they are functional and I haven't got round to fixing them yet).

    I have also had a lot of constraints to work around like implementing an automatic update method so when the users run it they always have the latest version of the project. I also had to have a method to kick off users and block them opening the database during a designated downtime (we have shift workers and I only work days) so I can manually do compact/repairs and any table level modifications.

  9. #9
    Richie27 is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Location
    Ireland
    Posts
    159
    In January, oh wow. So you're not using it very long at all then.

    Hmmm I started around March or April I think.

    Have you programmed a lot in other languages though? Or am I just a tad slow? ;-)

  10. #10
    R_Badger is offline Knows a few tricks
    Windows XP Access 2003
    Join Date
    Feb 2012
    Location
    Suffolk, UK
    Posts
    262
    Just saw your second post, a couple of things:

    I neglected to mention (it shouldn't make any difference but I always do it) set the report properties recordsouce to null.

    Also you only need the "" if its an sql string iirc so the following should work.

    Code:
    Private Sub Report_Open(Cancel As Integer)
    If [Forms]![frmFN_MainMenu]![txtSource] = "source1" Then
    Me.RecordSource = qryFN_Rep_FinalAction_EXT
    Else
    Me.RecordSource = qryFN_Rep_FinalAction_AW
    End If
    
    
    End Sub
    The source1/source2 things are just to allow the code to make a decision they could just as easily read random text 1 as long as they are different (I use this trick a fair bit it really does save on file size)

  11. #11
    Richie27 is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Location
    Ireland
    Posts
    159
    super cracked it - cheers!

  12. #12
    Richie27 is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Location
    Ireland
    Posts
    159
    Or not - would having one field different in the two queries make any difference do you think?

  13. #13
    R_Badger is offline Knows a few tricks
    Windows XP Access 2003
    Join Date
    Feb 2012
    Location
    Suffolk, UK
    Posts
    262
    I actually started in a few other languages way before this (BBC basic back in oooh 1990 i reckon), html, php, sql, vba are pretty much my lot at the moment, (and even those I'm still shaky with depending on how long its been since i last started thinking about it) I'm planing to add perl and python to my belt, but I'm in the middle of moving house at the moment so that wont be for a while yet!

  14. #14
    Richie27 is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Location
    Ireland
    Posts
    159
    1990 Wow - Did they even have computers back then.

    Tis a great way to keep your head occupied to be sure.

    Problem is - I often spend all day on a problem without resolution - then I'm like woah - it's 5pm and time to go home.

    I'd always get there eventually, problem is there isn't as much time as I like - which is why I thank God for this forum and people like yourself!

  15. #15
    R_Badger is offline Knows a few tricks
    Windows XP Access 2003
    Join Date
    Feb 2012
    Location
    Suffolk, UK
    Posts
    262
    Oh yes, although I was only 6 back then.....

    Believe it or not I usually post while I'm at work to distract myself from a problem I thinking about, sometimes I find I can't see the woods for the trees until I actually stop thinking about it.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Statement Syntax Error
    By Alex O in forum Access
    Replies: 8
    Last Post: 06-08-2012, 08:12 AM
  2. Hit Statement & Syntax Wall
    By Soule in forum Programming
    Replies: 5
    Last Post: 01-17-2012, 03:48 PM
  3. Replies: 7
    Last Post: 08-17-2011, 01:49 PM
  4. Case Statement Syntax
    By allenjasonbrown@gmail.com in forum Programming
    Replies: 1
    Last Post: 11-16-2010, 07:18 PM
  5. IIf Statement Syntax Error
    By shexe in forum Queries
    Replies: 2
    Last Post: 10-06-2010, 08:35 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums