Hi everyone,
I'm having troubles resolving a problem with a spreadsheet that pulls records data out of a data via SQL.
The original functionality was set up by someone else and I've unofficially inherited its running and improvement, as that person is no longer around.
The spreadsheet uses the SQL Select statement to pull information out of the database where it matches one of two fields (depending on what the user searches for).
The problem is that one of the fields ("Aliases") is in "Long Text" format as I needed more space than 255 characters for it. On importing, everything after 255 characters is truncated.
I've spent hours reading up on others' workarounds for their specific issues, but alas I cannot work out how their solutions work to apply it to my own.
Basically, I'd appreciate any specific help with someone explaining in very simple terms how to work around the character import limit in my specific scenario.
If the code below doesn't make sense, it might be because there are some functions listed in Public Subs. If you need me to put the code for these too, please LMK.
I've copied in just part of the code that forms the Select statement. I can include more of the code from this Sub too if I've not included enough.
I'm having troubles resolving a problem with a spreadsheet that pulls records data out of a data via SQL.
The original functionality was set up by someone else and I've unofficially inherited its running and improvement, as that person is no longer around.
The spreadsheet uses the SQL Select statement to pull information out of the database where it matches one of two fields (depending on what the user searches for).
The problem is that one of the fields ("Aliases") is in "Long Text" format as I needed more space than 255 characters for it. On importing, everything after 255 characters is truncated.
I've spent hours reading up on others' workarounds for their specific issues, but alas I cannot work out how their solutions work to apply it to my own.
Basically, I'd appreciate any specific help with someone explaining in very simple terms how to work around the character import limit in my specific scenario.
If the code below doesn't make sense, it might be because there are some functions listed in Public Subs. If you need me to put the code for these too, please LMK.
I've copied in just part of the code that forms the Select statement. I can include more of the code from this Sub too if I've not included enough.
Code:
db.Connect
If imerchantname <> "" And imerchantID = "" Then
strSQL = "SELECT tblMerchantID.[Scheme], tblMerchantID.[Currency], tblMerchantID.[Merchant Name], tblMerchantID.[ICA (Clearing)], tblMerchantID.[Clearing ID], tblMerchantID.[ACQ BIN (Auth)], tblMerchantID.[Authorisation ID], tblMerchantID.[MCC], tblMerchantID.[Expected Frequency], tblMerchantID.[Aliases], tblMerchantID.[Country], tblMerchantID.[Date/Time Added], tblMerchantID.[Date/Time Updated] " & vbCrLf & _
"FROM tblMerchantID " & vbCrLf & _
"GROUP BY tblMerchantID.[Scheme], tblMerchantID.[Currency], tblMerchantID.[Merchant Name], tblMerchantID.[ICA (Clearing)], tblMerchantID.[Clearing ID], tblMerchantID.[ACQ BIN (Auth)], tblMerchantID.[Authorisation ID], tblMerchantID.[MCC], tblMerchantID.[Expected Frequency], tblMerchantID.[Aliases], tblMerchantID.[Country], tblMerchantID.[Date/Time Added], tblMerchantID.[Date/Time Updated] " & vbCrLf & _
"HAVING (((tblMerchantID.[Merchant Name]) Like ""*" & imerchantname & "*"") OR ((tblMerchantID.[Aliases]) Like ""*" & imerchantname & "*""));"
End If
If imerchantname = "" And imerchantID <> "" Then
strSQL = "SELECT tblMerchantID.[Scheme], tblMerchantID.[Currency], tblMerchantID.[Merchant Name], tblMerchantID.[ICA (Clearing)], tblMerchantID.[Clearing ID], tblMerchantID.[ACQ BIN (Auth)], tblMerchantID.[Authorisation ID], tblMerchantID.[MCC], tblMerchantID.[Expected Frequency], tblMerchantID.[Aliases], tblMerchantID.[Country], tblMerchantID.[Date/Time Added], tblMerchantID.[Date/Time Updated] " & vbCrLf & _
"FROM tblMerchantID " & vbCrLf & _
"GROUP BY tblMerchantID.[Scheme], tblMerchantID.[Currency], tblMerchantID.[Merchant Name], tblMerchantID.[ICA (Clearing)], tblMerchantID.[Clearing ID], tblMerchantID.[ACQ BIN (Auth)], tblMerchantID.[Authorisation ID], tblMerchantID.[MCC], tblMerchantID.[Expected Frequency], tblMerchantID.[Aliases], tblMerchantID.[Country], tblMerchantID.[Date/Time Added], tblMerchantID.[Date/Time Updated] " & vbCrLf & _
"HAVING (((tblMerchantID.[Clearing ID]) Like ""*" & imerchantID & "*"") OR ((tblMerchantID.[Authorisation ID]) Like ""*" & imerchantID & "*""));"
End If
db.RunGetResults (strSQL)
