Member Avatar for mikeneedshelp

Hi,

I'm hoping that someone can help me out as I'm sort of new to programming.

I recorded a macro that uses microsoft query to get data from an access database. It works fine as it is, but I want to change the final filter to a variable and I can't seem to get the syntax right no matter what I try. Anyone have any ideas?

The part that I would like to replace with a variable is 'MMMX028762'.

[With ActiveSheet.QueryTables.Add(Connection:=Array(Array("ODBC;DSN=MS Access Database;DBQ=N:\FLEETDIS\Fleet Data References\Fleet DB\Utilization Database with NBVs.mdb;DefaultDir=N:\FLEETDIS"), Array("\Fleet Data References\Fleet DB;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;")), Destination:=Cells(4 + i, 8))
.CommandText = Array("SELECT `Total Fleet Current NA`.`Railcar ID`, `Total Fleet Current NA`.NBV, `Total Fleet Current NA`.`Adj NBV`" & Chr(13) & "" & Chr(10) & "FROM `Total Fleet Current NA` `Total Fleet Current NA`" & Chr(13) & "" & Chr(10) & "WHERE (`Total Fleet Current NA`.", "`Railcar ID`='MMMX028762')")
.Name = "Query from MS Access Database_1"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With]

Any help is most appreciated!!!!

  • 2 Contributors
  • 2 Replies
  • 108 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by mikeneedshelp
Member Avatar for hkdani

.CommandText = Array("SELECT `Total Fleet Current NA`.`Railcar ID`, `Total Fleet Current NA`.NBV, `Total Fleet Current NA`.`Adj NBV`" & Chr(13) & "" & Chr(10) & "FROM `Total Fleet Current NA` `Total Fleet Current NA`" & Chr(13) & "" & Chr(10) & "WHERE (`Total Fleet Current NA`.", "`Railcar ID`='MMMX028762')")

You're wanting to replace part of a string: or the part after Railcar ID.

Just declare a couple string variables.

Dim strSequel as string, strID as string
strSequel = "SELECT `Total Fleet Current NA`.`Railcar ID`, `Total Fleet Current NA`.NBV, `Total Fleet Current NA`.`Adj NBV`" & Chr(13) & "" & Chr(10) & "FROM `Total Fleet Current NA` `Total Fleet Current NA`" & Chr(13) & "" & Chr(10) & "WHERE (`Total Fleet Current NA`.", "`Railcar ID`='"

strID = "Whatever"

strSequel = strSequel & strID & "')"

.CommandText = Array(strSequel)
Member Avatar for mikeneedshelp

Nice simple fix. I like it.

Thanks,
Mike

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.