SQL Statement doesn't translate to Microsoft Query

[johnisotank]

Hi All,

My apologies if this is posted in the wrong forum.

I have an Excel 2003 spreadsheet with data retrieved from an SQL server through Microsoft Query.

In my SQL server my SELECT statement is

SELECT field1, field2 
FROM myTable
WHERE (field1 ='' OR field1 = @Parameter)

So this means if a parameter is supplied then list all records meeting that param or otherwise show all records.

When I try this using Microsoft Query I get the error:

'STRING DATA RIGHT TRUNCATION'

Can anyone advise a workaround for this pls.

Thanks
John

 

[SkipVought]

Hi,

Parameters in MS Query...

SELECT field1, field2 
FROM myTable
WHERE (field1 ='' OR field1 = [Your Prompt])

Edit > Return data to Microsoft Excel

Supply some data for the prompt.

Data > Import External Data > Parameters -- Here you have THREE options for supplying a parameter.

Optionally, do it all in code like this...

Sub test()
    Dim sSQL As String
    
    sSQL = "SELECT field1, field2"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "FROM [myTable]"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "WHERE (field1 ='' OR field1 ='" & [YourCellWithValue] & "'"
    
    With Sheets("YourSheet").QueryTables("YourQueryTableName")
        .CommandText = sSQL
        .Refresh False
    End With
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[johnisotank]

Hi Skip, thanks for the quick reply.

The MQ parameters throw the error as in my first post.

I tried your second post which works when there is a value in my cell A1 but if there is no value then it shows no records.

here is my code to suit my sheet...

Sub test()
   Dim sSQL As String
    sSQL = "SELECT JobNo, Type, CustCode"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "FROM tblLift"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "WHERE (CustCode ='' OR CustCode ='" & Range("B1").Value & "')"
        With Sheets("M130").QueryTables(1)
        .CommandText = sSQL
        .Refresh False
    End With
End Sub

Maybe I should be entering something in that cell other than nothing?

Thanks
John

 

[SkipVought]

is tblLift a NAMED RANGE in your workbook?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[johnisotank]

No, it is simply one of my SQL Server 2005 database tables.

 

[SkipVought]

You must NAME the table, using the Name Box or Insert > Name > Define

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[johnisotank]

Hi, I've not named the range but I have managed to get the blank entries working by using a simple IF formula to edit the WHERE section of the SQL Statement so thanks for pointing me in the right direction for that.

I do have one last problem though if you wouldn't mind helping me.

I am trying to filter my results by a date range. I have cells named DateFrom and DateTo and they are in the format dd/MM/yyyy HH:mm. But if choose the following date range:

DateFrom: 04/10/2008 00:00
DateTo: 04/10/2008 23:59

it returns jobs dated 10/04/2008.

Do you know of a simple fix for this?

Thanks
John

 

[SkipVought]

faq68-5827.

Look at your format.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[johnisotank]

Hi Skip, thanks for the link.

It didn't really help me out that much (my formatting looked ok) but I've got it working now by changing the dd/mm/yyyy hh:mm format into SQL YYYYMMDD HHMM.

But thank you very much for your help.

John