puzzling syntax error in this query

[BrockWade]

I have a table "BankDebits" (containing three fields: Store, Amount, Date) from which I am trying to populate the contents into a temp table "tmpBankDebitConcatenate" (containg four fields: Store, Amount, DebitDate, and ConcatenateBankDebits). Note: I am, for other reasons than are evident here, having to format a date during this query from dd/mm/yyyy to mm/dd/yy and also having to form the concatenation field for a comparison in another table... any clues? It is saying i am missing an operator:

INSERT INTO tmpBankDebitConcatenate



SELECT

BankDebits.Store AS Store,
BankDebits.Amount AS Amount,

(((((Left (BankDebits.Date, 2)) AS DebitDay,
((Mid (BankDebits.Date, 4, 2)) AS DebitMonth,
((Right (BankDebits.Date, 2)) AS DebitYear),
DebitMonth + '/' + DebitDay + '/' + DebitYear)
AS DebitDate,

Store & " " & Amount & " " & DebitDate AS ConcatenateBankDebits

FROM BankDebits;

[rpeare]

can't you do something like

format([datefield], "mm/dd/yyyy")

[June7]

Is the original date saved in date field or text field? http://allenbrowne.com/ser-36.html


INSERT INTO tmpBankDebitConcatenate(Store, Amount, DebitDate, ConcatenateBankDebits)

SELECT

Store,

Amount,

DateSerial(Right([Date], 4), Mid([Date], 4, 2), Left([Date], 2)) AS DebitDate,

Store & " " & Amount & " " & DateSerial(Right([Date], 4), Mid([Date], 4, 2), Left([Date], 2)) AS ConcatenateBankDebits

FROM BankDebits;

[BrockWade]

"BankDebits" table is a linked Excel file that itself cannot be reformatted... the date field is sset up as a text field.

[rpeare]

format(cdate([datefield]), "mm/dd/yyyy")

[June7]

rpeare, not sure that will work because OP said the Excel text value is dd/mm/yyyy.

For example

CDate("5/12/2013") will return 5/12/2013, not 12/5/2013

whereas

CDate("31/12/2013") will return 12/31/2013

[rpeare]

You're right june I misread so you'd have to do the string parsing.

[BrockWade]

Thanks.... making real progress thanks to you guys... is there a way to edit "DateSerial(Right([Date], 4), Mid([Date], 4, 2), Left([Date], 2)) AS DebitDate" To produce the date in the format of 04/15/13?... my real goal which may not require altering this string is that I need this displayed in a report as mm/dd/yy instead of mm/dd/yyyy... is there a way of doing this on the query that drives the report without editing the already working well string above?

[June7]

Since the expression converts the value to a date, you can wrap it into a Format function or set the Format property of textbox.

Or don't bother converting to actual date value to begin with, if you aren't doing any date calcs.

Mid([Date], 4, 2) & "/" & Left([Date], 2) & "/" & Right([Date], 2) AS DebitDate

Note the & is the preferred concatenation operator.

[BrockWade]

I'm trying to edit your solution to give me the needed results of a format like 10/04/13 and I'm struggling with the SQL that feeds the report:

SELECT DISTINCT tmpBankDebitConcatenate.Store, tmpBankDebitConcatenate.Amount,
tmpBankDebitConcatenate.DebitDate,

Left([DebitDate],2) & "/" & Mid([DebitDate],4,2) & "/" & Right([DebitDate], 2) AS DebitDate1

FROM tmpBankDebitConcatenate LEFT JOIN tmpBankConcatenate ON tmpBankDebitConcatenate.Amount = tmpBankConcatenate.Amount
WHERE (((tmpBankConcatenate.Store) Is Not Null) AND (tmpBankDebitConcatenate.DebitDate <> tmpBankConcatenate.DepositDate));

[June7]

Why doesn't it work - error message, wrong results?

Are you importing into a text field or a date field?

Did you read Allen Browne's article?

If you import into a date field, I think your data will be corrupted.

Do you want the report to display MM/DD/YY or DD/MM/YY?

[BrockWade]

It is almost giving the correct results... very consistently with such dates as 10/17/13 but with some other dates it is giving 11/1//13 when it should be 11/01/13.

[June7]

The explanation is with Allen Browne's article.

Do you want to answer my other questions?

[BrockWade]

I read Allen Browne's article and his website has helped me greatly in the last 12 years, but the article as I interpreted it, I failed to see the connection. As to the other questions:

Why doesn't it work - error message, wrong results? wrong results, no errors

Are you importing into a text field or a date field? text field

Did you read Allen Browne's article? Yes

If you import into a date field, I think your data will be corrupted. Understood

Do you want the report to display MM/DD/YY or DD/MM/YY? MM/DD/YY with zeros included e.g. 11/04/13

[June7]

The connection is that 4/12/2013 is just as valid a date as 12/4/2013 and Access will not 'adjust'. Unlike 31/12/2013, which Access will adjust to 12/31/2013 if import to a date field.

Then the next question: Is the import preserving the value as dd/mm/yyyy in the text field?

If yes, then the parsing should be:

Mid([DebitDate],4,2) & "/" & Left([DebitDate],2) & "/" & Right([DebitDate], 2) AS DebitDate1

However, that assumes the Excel text has placeholder zeros that are retained in the import. If not, this gets more complicated.

Can you provide the Excel file?