UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> If Statement With Query    
 
   
cyberfletch
post Mar 25 2019, 12:33 PM
Post#1



Posts: 22
Joined: 25-April 15



I have an Mail Merge section which I am trying to get an if statement to work but keep coming up with an error (expected end of statement on my first line. I would like to see if anyone can help me figure out what I a m doing wrong. All of my Dim's are correct and all other code in this works well..

CODE
iif (([user].[UserID] = fosUsername()) and ([user].[Gov] = -1)) = true then
    Set rst = dbs.OpenRecordset("SELECT User.UserNamePOC, User.Title as TitlePOC, User.UserEmail as UserEmailPOC, User.UserPhone as UserPhonePOC, " & _
                                "User.Agency, User.Command, User.Organization, " & _
                                "User.Address as AddressPOC, User.City as CityPOC, User.State as StatePOC, User.ZIPCode as ZIPCodePOC " & _
                                "FROM [User]" & _
                                "WHERE (((User.UserID)=fOSUserName()) AND ((User.Gov)=-1));")
                                
                                'rst.MoveFirst
                                Do Until rst.EOF = True
                                
                                    Me.[UserNamePOC] = rst![UserNamePOC]
                                    Me.[TitlePOC] = rst![TitlePOC]
                                    Me.[UserEmailPOC] = rst![UserEmailPOC]
                                    Me.[UserPhonePOC] = rst![UserPhonePOC]
                                    Me.[Agency] = rst![Agency]
                                    Me.[Command] = rst![Command]
                                    Me.[Organization] = rst![Organization]
                                    Me.[AddressPOC] = rst![AddressPOC]
                                    Me.[CityPOC] = rst![CityPOC]
                                    Me.[StatePOC] = rst![StatePOC]
                                    Me.[ZIPCodePOC] = rst![ZIPCodePOC]

                                
Else

    Set rst = dbs.OpenRecordset("SELECT User.UserNamePOC, User.Title as TitlePOC, User.UserEmail as UserEmailPOC, User.UserPhone as UserPhonePOC, " & _
                                "User.Agency, User.Command, User.Organization, " & _
                                "User.Address as AddressPOC, User.City as CityPOC, User.State as StatePOC, User.ZIPCode as ZIPCodePOC " & _
                                "FROM [User]" & _
                                "WHERE (((User.GovPOC)=-1));")

                                'rst.MoveFirst
                                Do Until rst.EOF = True
                                
                                    Me.[UserNamePOC] = rst![UserNamePOC]
                                    Me.[TitlePOC] = rst![TitlePOC]
                                    Me.[UserEmailPOC] = rst![UserEmailPOC]
                                    Me.[UserPhonePOC] = rst![UserPhonePOC]
                                    Me.[Agency] = rst![Agency]
                                    Me.[Command] = rst![Command]
                                    Me.[Organization] = rst![Organization]
                                    Me.[AddressPOC] = rst![AddressPOC]
                                    Me.[CityPOC] = rst![CityPOC]
                                    Me.[StatePOC] = rst![StatePOC]
                                    Me.[ZIPCodePOC] = rst![ZIPCodePOC]

End If
                                
                                rst.MoveNext
                                Loop

--------------------
Doug<>

US NAVY Retired

Database developer
Go to the top of the page
 
theDBguy
post Mar 25 2019, 12:36 PM
Post#2


Access Wiki and Forums Moderator
Posts: 76,073
Joined: 19-June 07
From: SunnySandyEggo


Hi Doug. I think you want to use If instead of IIf().

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
cyberfletch
post Mar 25 2019, 12:41 PM
Post#3



Posts: 22
Joined: 25-April 15



Cant beleive i missed that. Thank you for the quick answere. That cleared my error.

So if i get this right, if it is in VB then it is if, anything else it is iif?

--------------------
Doug<>

US NAVY Retired

Database developer
Go to the top of the page
 
tina t
post Mar 25 2019, 12:43 PM
Post#4



Posts: 6,033
Joined: 11-November 10
From: SoCal, USA


QUOTE
iif (([user].[UserID] = fosUsername()) and ([user].[Gov] = -1)) = true then

hello Doug, a couple things here:

IIf is a function, IIf(), which you can use in queries, and expressions in form/report controls, macro conditions, etc. in VBA you're using the If statement, as

If ... Then
...
ElseIf ... Then
...
Else
....
End If
(EDIT: and what theDBguy said... :)

next, let's get rid of all the parentheses and the extra "= true" in the criteria expression, as

If [user].[UserID] = fosUsername() and [user].[Gov] = -1 Then

and finally, what is [user]? if that's the name of the table or query that's used as the form's RecordSource, use the Me reference instead, as

Me!UserID

if [user] refers to a table or query that is not the form's RecordSource, i doubt that you can refer to it that way in VBA - unless you're doing something i'm not familiar with. so, what is [user], and how is it connected to the form?

hth
tina
This post has been edited by tina t: Mar 25 2019, 12:45 PM

--------------------
"the wheel never stops turning"
Go to the top of the page
 
theDBguy
post Mar 25 2019, 12:43 PM
Post#5


Access Wiki and Forums Moderator
Posts: 76,073
Joined: 19-June 07
From: SunnySandyEggo


Hi. Not quite. You can also use IIf() in VBA. Basically, the If/Then code block and the IIf() function are used for different purposes.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
cyberfletch
post Mar 25 2019, 12:51 PM
Post#6



Posts: 22
Joined: 25-April 15



Got it.

Now i have another question about the [User].[Gov] = -1. It sayes that the Gov field is not defined.

Compile Error
External Name Not Defined

This field is a check box in the table. Do I need to define it with a Dim? and if so how do you define a check box.


this code populate a form to be used for mailmerge. i have a lot more code in this form but this is new to the form.
This post has been edited by cyberfletch: Mar 25 2019, 12:56 PM

--------------------
Doug<>

US NAVY Retired

Database developer
Go to the top of the page
 
tina t
post Mar 25 2019, 12:57 PM
Post#7



Posts: 6,033
Joined: 11-November 10
From: SoCal, USA


QUOTE
Now i have another question about the [User].[Gov] = -1. It sayes that the Gov field is not defined.

QUOTE
what is [user]? if that's the name of the table or query that's used as the form's RecordSource, use the Me reference instead, as

Me!UserID

if [user] refers to a table or query that is not the form's RecordSource, i doubt that you can refer to it that way in VBA - unless you're doing something i'm not familiar with. so, what is [user], and how is it connected to the form?

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
cyberfletch
post Mar 25 2019, 01:01 PM
Post#8



Posts: 22
Joined: 25-April 15



User is a lookup table in the database and is not connected to the form.

Below is the top of my code.

There are 40 different queries which populate the unbound form. All in VB.

We use this to fill out a survey that we send out.

CODE
Private Sub Form_Load()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim tmpUser As String
Dim strSQL As String
Dim strCage As String
Dim strICQBY As String
Dim strBY As String
Dim strProduct As String



Set dbs = CurrentDb

This post has been edited by cyberfletch: Mar 25 2019, 01:02 PM

--------------------
Doug<>

US NAVY Retired

Database developer
Go to the top of the page
 
tina t
post Mar 25 2019, 04:30 PM
Post#9



Posts: 6,033
Joined: 11-November 10
From: SoCal, USA


QUOTE
If [user].[UserID] = fosUsername() and [user].[Gov] = -1 Then

okay. you can't refer to a table directly that way, in VBA - or in a form/report for that matter - unless it's the form's RecordSource. and then, as i mentioned before, you'd use the Me reference, not the table name.

you have to either a) open a recordset against table [user] before the If statement criteria, and then refer to the recordset, not the table name, or b) use DLookup() to retrieve the value of field [Gov] where [UserID] = fosUsername(), and run the If statement against the return value of the DLookup().

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
cyberfletch
post Mar 28 2019, 12:38 PM
Post#10



Posts: 22
Joined: 25-April 15



This topic is closed.

I asked a coworker and he helped me figure out what I was doing wrong. Below is what we came up with and it works great!! woohoo.gif

CODE
    Set rst = dbs.OpenRecordset("SELECT User.Gov, User.GovPOC FROM [User] WHERE (((User.UserID)=fOSUserName()));")
            mGov = rst!Gov
            mGovPOC = rst!GovPOC
         '  mGov = DLookup("Gov", "[User]", "UserID = '" & fOSUserName() & "'")
         '  mGovPOC = DLookup("GovPOC", "[User]", "UserID = '" & fOSUserName() & "'")
'If User.UserID = fOSUserName() And mGov = -1 = True Then
    Set rst = dbs.OpenRecordset("SELECT User.UserNamePOC, User.Title as TitlePOC, User.UserEmail as UserEmailPOC, User.UserPhone as UserPhonePOC, " & _
                                "User.Agency, User.Command, User.Organization, " & _
                                "User.Address as AddressPOC, User.City as CityPOC, User.State as StatePOC, User.ZIPCode as ZIPCodePOC " & _
                                "FROM [User]" & _
                                "WHERE (((User.UserID)=fOSUserName()) AND ((User.Gov)=-1));")
                                
                                'rst.MoveFirst
                                Do Until rst.EOF = True
                                
                                    Me.[UserNamePOC] = rst![UserNamePOC]
                                    Me.[TitlePOC] = rst![TitlePOC]
                                    Me.[UserEmailPOC] = rst![UserEmailPOC]
                                    Me.[UserPhonePOC] = rst![UserPhonePOC]
                                    Me.[Agency] = rst![Agency]
                                    Me.[Command] = rst![Command]
                                    Me.[Organization] = rst![Organization]
                                    Me.[AddressPOC] = rst![AddressPOC]
                                    Me.[CityPOC] = rst![CityPOC]
                                    Me.[StatePOC] = rst![StatePOC]
                                    Me.[ZIPCodePOC] = rst![ZIPCodePOC]
                                
                                rst.MoveNext
                                Loop

                                
'ElseIf mGovPOC = -1 = True Then
If mGov = 0 Then
    Set rst = dbs.OpenRecordset("SELECT User.UserNamePOC, User.Title as TitlePOC, User.UserEmail as UserEmailPOC, User.UserPhone as UserPhonePOC, " & _
                                "User.Agency, User.Command, User.Organization, " & _
                                "User.Address as AddressPOC, User.City as CityPOC, User.State as StatePOC, User.ZIPCode as ZIPCodePOC " & _
                                "FROM [User]" & _
                                "WHERE (((User.GovPOC)=-1));")

                                'rst.MoveFirst
                                Do Until rst.EOF = True
                                
                                    Me.[UserNamePOC] = rst![UserNamePOC]
                                    Me.[TitlePOC] = rst![TitlePOC]
                                    Me.[UserEmailPOC] = rst![UserEmailPOC]
                                    Me.[UserPhonePOC] = rst![UserPhonePOC]
                                    Me.[Agency] = rst![Agency]
                                    Me.[Command] = rst![Command]
                                    Me.[Organization] = rst![Organization]
                                    Me.[AddressPOC] = rst![AddressPOC]
                                    Me.[CityPOC] = rst![CityPOC]
                                    Me.[StatePOC] = rst![StatePOC]
                                    Me.[ZIPCodePOC] = rst![ZIPCodePOC]
                                
                                rst.MoveNext
                                Loop

--------------------
Doug<>

US NAVY Retired

Database developer
Go to the top of the page
 
theDBguy
post Mar 28 2019, 12:48 PM
Post#11


Access Wiki and Forums Moderator
Posts: 76,073
Joined: 19-June 07
From: SunnySandyEggo


Hi Doug. Congratulations! Glad to hear you got it sorted out. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    23rd August 2019 - 05:57 AM