Call Module within query 1

[jw5107]

Below is what I'm working with....
I'm trying to learn how to call a procedure/module from a query in VB...
Any suggestions or examples..?
Thanks...!!
jw5107

Below is the SQL from an append query created using the wizard - "RemoveSpaces" is the procedure/module I'm tryin' to call....

INSERT INTO WWRHistoryPrep ( WWRID, IID, AllocMstr )
SELECT WWReviewHistory.WWRID, WWReviewHistory.IID, IIf(nz([Allocated])<>"",[IID] & " " & RemoveSpaces([Allocated]),[IID] & " " & [Allocated]) AS AllocMstr
FROM WWReviewHistory
ORDER BY WWReviewHistory.WWRID DESC;

 

[Remou]

You should not need an extra function to remove spaces, Replace should do it.

Replace(Allocated," ","")

 

[Golom]

INSERT INTO WWRHistoryPrep ( WWRID, IID, AllocMstr )
SELECT WWRID, IID, 
IIf(NZ([Allocated])<>"",
[IID] & " " & RemoveSpaces([Allocated]),
[IID] ) AS AllocMstr
FROM WWReviewHistory

If [blue]NZ([Allocated])<>""[/blue] evaluates to FALSE then the third clause if the IIF (i.e. the FALSE Condition) should not include the [Allocated] field because it is blank.

Is to your question ...

RemoveSpaces needs to be a public function in a module of the form

Public Function RemoveSpaces(myVar As String) As String
[COLOR=black cyan]' Code to remove spaces[/color]
RemoveSpaces = SomeValue
End Function

 

[jw5107]

Golom,

I have tried your example... And I'm still having troubles... I keep getting syntax errors...

This is what I got:

DoCmd.RunSQL "INSERT INTO WWRHistoryPrep (WWRID, IID, AllocMstr) SELECT WWRID, IID, IIf(Nz([Allocated])<>"", [IID] & RemoveSpace([Allocated]), [IID] & [Allocated]) AS AllocMstr FROM WWReviewHistory

 

[Golom]

DoCmd.RunSQL "INSERT INTO WWRHistoryPrep (WWRID, IID, AllocMstr) " & _
"SELECT WWRID, IID, Replace([Allocated],"" "","""") AS AllocMstr " & _
FROM WWReviewHistory"

Using Remou's approach

 

[jw5107]

Golom,

Just AWESOME..!!!!!
I really appreciate it...!!!!!

How much different would a simialar query be if I were to add this module:
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String

Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

I currently use this in a query - to call the above module:
Concatenate("SELECT GTWY & '('& INSTALLS &')' FROM UsageCnts WHERE IID =""" & [IID] & """ ORDER BY INSTALLS")

How can I get a query like you just taught me to implement the module above....??
Thanks for your quick responses AND the lesson...!!!
Star for ya..!!
jw5107

 

[Golom]

DoCmd.RunSQL _
"INSERT INTO WWRHistoryPrep (WWRID, IID, AllocMstr) " & _
"SELECT WWRID, IID, Replace([Allocated],"" "","""") AS AllocMstr, " & _
"Concatenate(""SELECT GTWY & '(' & INSTALLS  & ')' " & _
              "FROM  UsageCnts " & _
              "WHERE IID =" & W.[IID] & " " & _
              "ORDER BY INSTALLS"") As [ConcatenatedStuff] " & _
"FROM WWReviewHistory W"

I think I got the quotes right.

 

[jw5107]

Golom,

Below is what I'm working with per your example...
Thanks again by the way..!!!

The query below erros and highlights [IID] stating its an external name that can't be defined....???

DoCmd.RunSQL _
"INSERT INTO GTWYUsage ( IID, InstallGTWYs ) " & _
"SELECT [4qryUsageCntsIID].IID, " & _
"Concatenate(""SELECT GTWY & '('& INSTALLS &')' " & _
"FROM UsageCnts " & _
"WHERE IID =""" & [IID] & " " & _
"ORDER BY INSTALLS"") AS GTWYsUsage " & _
"FROM 4qryUsageCntsIID;"

jw5107