IIF Statement in VBA

  • Thread starter Thread starter Jacinto Muchine
  • Start date Start date
J

Jacinto Muchine

Hello,

Ive created a query that creates an incremental number
depending on a particular field using IIF Statement.The
query runs well. Now I'd like to run the query after an
update event therefore I, Ive done the following but it
always highlights the "000" and says "expected end of
statement". The code is bellow, can any one help please?

Dim bidDisplay As String
Me.Refresh

If Bid = True Then

BidDisplay = "SELECT First(IIf([Opportunity].[bid],
[industryTypeId]+ Format([Opportunity].
[opportunityId],"000"),"")) AS Bid_Reference FROM Company
INNER JOIN Opportunity ON Company.companyId =
Opportunity.companyId GROUP BY Opportunity.Description,
Opportunity.opportunityId, Opportunity.Bid HAVING
(((Opportunity.opportunityId)=[Forms]![frmCompany]!
[Child14].[Form]![opportunityId]) AND ((Opportunity.Bid)
=Yes))"

BidNumber = bidDisplay
Me.Refresh
End If


Thanks
Jacinto
 
The problem is that you're building a text string, and you want to use
imbedded " characters. However, ACCESS doesn't know which ones are to
imbedded and which ones indicate the start and end of strings.

So, double up the " characters that are to be imbedded; two " characters in
a row tell ACCESS to use one literal " character in the text string.

BidDisplay = "SELECT First(IIf([Opportunity].[bid],
[industryTypeId]+ Format([Opportunity].
[opportunityId],""000""),"""")) AS Bid_Reference FROM Company
INNER JOIN Opportunity ON Company.companyId =
Opportunity.companyId GROUP BY Opportunity.Description,
Opportunity.opportunityId, Opportunity.Bid HAVING
(((Opportunity.opportunityId)=[Forms]![frmCompany]!
[Child14].[Form]![opportunityId]) AND ((Opportunity.Bid)
=Yes))"
 
Thanks A lot Ken, that was very useful!!!

On top of that, I'd like to display the result of that
query on a text box, named "bidNumber". I've put the
"First" function so the result is only one. But it does not
seem t display. Any help will be very appreciated!

Thanks
Jacinto
-----Original Message-----
The problem is that you're building a text string, and you want to use
imbedded " characters. However, ACCESS doesn't know which ones are to
imbedded and which ones indicate the start and end of strings.

So, double up the " characters that are to be imbedded; two " characters in
a row tell ACCESS to use one literal " character in the text string.

BidDisplay = "SELECT First(IIf([Opportunity].[bid],
[industryTypeId]+ Format([Opportunity].
[opportunityId],""000""),"""")) AS Bid_Reference FROM Company
INNER JOIN Opportunity ON Company.companyId =
Opportunity.companyId GROUP BY Opportunity.Description,
Opportunity.opportunityId, Opportunity.Bid HAVING
(((Opportunity.opportunityId)=[Forms]![frmCompany]!
[Child14].[Form]![opportunityId]) AND ((Opportunity.Bid)
=Yes))"

--
Ken Snell
<MS ACCESS MVP>


Hello,

Ive created a query that creates an incremental number
depending on a particular field using IIF Statement.The
query runs well. Now I'd like to run the query after an
update event therefore I, Ive done the following but it
always highlights the "000" and says "expected end of
statement". The code is bellow, can any one help please?

Dim bidDisplay As String
Me.Refresh

If Bid = True Then

BidDisplay = "SELECT First(IIf([Opportunity].[bid],
[industryTypeId]+ Format([Opportunity].
[opportunityId],"000"),"")) AS Bid_Reference FROM Company
INNER JOIN Opportunity ON Company.companyId =
Opportunity.companyId GROUP BY Opportunity.Description,
Opportunity.opportunityId, Opportunity.Bid HAVING
(((Opportunity.opportunityId)=[Forms]![frmCompany]!
[Child14].[Form]![opportunityId]) AND ((Opportunity.Bid)
=Yes))"

BidNumber = bidDisplay
Me.Refresh
End If


Thanks
Jacinto
Click to expand...


.
Click to expand...
 
You can't set the control source nor the value of a textbox to an SQL query
statement. What you need to do is to open the recordset in code, then set
the value of the field to the textbox:

Dim bidDisplay As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Me.Refresh

If Bid = True ThenBidDisplay = "SELECT First(IIf([Opportunity].[bid],
[industryTypeId]+ Format([Opportunity].
[opportunityId],""000""),"""")) AS Bid_Reference FROM Company
INNER JOIN Opportunity ON Company.companyId =
Opportunity.companyId GROUP BY Opportunity.Description,
Opportunity.opportunityId, Opportunity.Bid HAVING
(((Opportunity.opportunityId)=[Forms]![frmCompany]!
[Child14].[Form]![opportunityId]) AND ((Opportunity.Bid)
=Yes))"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(BidDisplay)
If rst.BOF = False And rst.EOF = False Then
rst.MoveFirst
BidNumber = rst!Bid_Reference
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
Me.Refresh
End If

--
Ken Snell
<MS ACCESS MVP>

Jacinto Muchine said:
Thanks A lot Ken, that was very useful!!!

On top of that, I'd like to display the result of that
query on a text box, named "bidNumber". I've put the
"First" function so the result is only one. But it does not
seem t display. Any help will be very appreciated!

Thanks
Jacinto
-----Original Message-----
The problem is that you're building a text string, and you want to use
imbedded " characters. However, ACCESS doesn't know which ones are to
imbedded and which ones indicate the start and end of strings.

So, double up the " characters that are to be imbedded; two " characters in
a row tell ACCESS to use one literal " character in the text string.

BidDisplay = "SELECT First(IIf([Opportunity].[bid],
[industryTypeId]+ Format([Opportunity].
[opportunityId],""000""),"""")) AS Bid_Reference FROM Company
INNER JOIN Opportunity ON Company.companyId =
Opportunity.companyId GROUP BY Opportunity.Description,
Opportunity.opportunityId, Opportunity.Bid HAVING
(((Opportunity.opportunityId)=[Forms]![frmCompany]!
[Child14].[Form]![opportunityId]) AND ((Opportunity.Bid)
=Yes))"

--
Ken Snell
<MS ACCESS MVP>


Hello,

Ive created a query that creates an incremental number
depending on a particular field using IIF Statement.The
query runs well. Now I'd like to run the query after an
update event therefore I, Ive done the following but it
always highlights the "000" and says "expected end of
statement". The code is bellow, can any one help please?

Dim bidDisplay As String
Me.Refresh

If Bid = True Then

BidDisplay = "SELECT First(IIf([Opportunity].[bid],
[industryTypeId]+ Format([Opportunity].
[opportunityId],"000"),"")) AS Bid_Reference FROM Company
INNER JOIN Opportunity ON Company.companyId =
Opportunity.companyId GROUP BY Opportunity.Description,
Opportunity.opportunityId, Opportunity.Bid HAVING
(((Opportunity.opportunityId)=[Forms]![frmCompany]!
[Child14].[Form]![opportunityId]) AND ((Opportunity.Bid)
=Yes))"

BidNumber = bidDisplay
Me.Refresh
End If


Thanks
Jacinto
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top