SQL problem newbie

[sven2]

Hello,

I have a problem with an SQL statement.

The thing is that I want to use a union on a tinyinteger field with text.

Something like this:

strSQL = " SELECT DISTINCT Gramgewicht " & _
" FROM TblPapiersoorten " & _
" UNION SELECT 'ALLE GRAMGEWICHTEN' AS Gramgewicht " & _
" FROM tblpapiersoorten " & _
" WHERE (MachineID = " & [Forms]![FrmRapportering]![txtmachineID] & ") AND (Papiersoort = '" & [Forms]![FrmRapportering]![txtPapiersoort] & "')"

But the problem is that gramgewicht is tinyinteger and 'alle gramgewichten' is text. How can I solve thsi problem?

Thanks in advance,
Sven.

 

[namliam]

Convert the GramGewicht to a string ...

I dont see the point of this tho.... why do this? It doesnt seem logical to me...

Special greets to a fellow Dutchman !

 

[sven2]

Why do this?

Because this is a list to choose from that is used for a query and I want to have the possibility to select "all gramgewichten"

Best regards,
Sven.

 

[namliam]

Ah yes, that makes sence ...

Like I said, convert the gramgewichten to a string using Cstr or Format function

 

[sven2]

Hello,

I'm back again because I can't get it to work ...
Where Do I put this Cstr ???

I tried it like this:

strSQL = " SELECT DISTINCT Cstr([Gramgewicht]) " & _
" FROM TblPapiersoorten " & _
" UNION SELECT 'ALLE GRAMGEWICHTEN' AS Gramgewicht " & _
" FROM tblpapiersoorten " & _
" WHERE (MachineID = " & [Forms]![FrmRapportering]![txtmachineID] & ") AND (Papiersoort = '" & [Forms]![FrmRapportering]![txtPapiersoort] & "')"

But is doesn't work!

Thank in advance,
Sven.

 

[namliam]

what doesnt work?

You applied the Cstr to convert the integer to string, union on all strings should work.
Or is the output different from what you expect?

What is the FULL code?

 

[sven2]

Hello,

I think it doesn't work because I am working on SQL tables and not on access tables. So the error that I get is that Cstr is not a defined function!

So it is my mistake because I had to mention this in my first message ...
Sorry ...

So I need the function for SQL tables ...

Best regards,
Sven.

 

[sven2]

Hello,

I get it to work with the str function ...
Thanks

Sven.

 

[namliam]

I am not big on SQL Server, sorry...

But try google....

http://www.google.nl/search?hl=nl&q=sql+server+convert+to+string&meta=

 

[tehNellie]

CAST or CONVERT are your T-SQL friends when it comes to converting datatypes.