Rusty, not used VS for 5 months!

[hog]

I am trying to use the query builder to concatenate an autonumber field with a date field so the are displayed in a combo box.

 

SELECT [log_no] & " " & [dated] AS logno
FROM tblLogNumbers;

 

works fine in Access, but not in VS?

 

I have tried many attempts including;

 

SELECT str(log_no) + ' ' + str(dated AS logno
FROM tblLogNumbers;

 

which all fail:(

[Joe Mamma]

SELECT Cast([log_no] as varchar(255) ) + ' ' + Cast([dated] as varchar(255) ) AS logno

FROM tblLogNumbers

[hog]

Nope, that dont work either I get an error message to do with the As statment and to do with one of the )

[Nerseus]

You can't use Cast in Access (that's SQL Server syntax). In Access you must use Format, something like:

SELECT Format(log_no) + ' ' + Format(dated) AS logno
FROM tblLogNumbers

 

Your original code, using Str, should work too but you were missing a closing parenthesis.

 

If this doesn't work in ".NET", then you'll have to show us the code you're using to call Access (DataReader, DataSet with DataAdapter, etc.).

 

-ner

[hog]

nope:(

 

error message states string not recognized as valid date along with loads of other errors???

 

re the missing ) that was a typo here, my code did have it in it tho:(

[Joe Mamma]

sorry. . misread. . . does this work?

 

SELECT cstr(log_no) + ' ' + Format([dated],"yyyymmdd") AS logno

FROM tblLogNumbers

[hog]

I get this error :

 

couldn't store <130/06/2004> in logno column expected type is date time'

 

:confused:

[PlausiblyDamp]

probably because there aren't 130 days in june, or only 12 months in a year - the 130 is probably wrong

[hog]

Think he means <30/06/2004>

 

This is his son, hes texting me what to post and he had 130 must of been typo

[Nerseus]

Nope, I think 130 is right.

 

You're getting an error filling a DataSet, I assume, where the column that you're matching to is defined as a DateTime. I'm betting your DataSet is predefined (from an XSD file maybe) and the logno column is defined as DateTime.

 

You're attempting to piece together a Number and a Date and return that as a string from the Database. So if log_no is 1 and dated is "6/30/2004" then I'd expect:

130/6/2004

 

The first "1" is the log_no, the "30/6/2004" is the date.

 

I have no idea why you'd want to piece together a number and date as ONE column in your DataSet... but your DataSet better have that defined as a string column - and I'm guessing it's not.

 

-ner

[hog]

OK I hve is sussed;

 

SELECT     '[' + cstr(log_no) + '] ' + format(logged_dated, 'dd/mm/yyyy') + ' ' + LEFT(description, 40) AS log_detaill, log_no
FROM         tblLog
ORDER BY log_no

 

I am doing this so that the log detail combo box displays the log number, the date and the first 40 characters of the description in one column.

 

thus..... [23] 30/8/2004 This is some text from the log

 

Thnx:)