Error in queries after migration/conversion

[Krutika]

These were the queries in MS Access. After conversion they are showing
errors. How to solve these?

SELECT
tblCurrent.AutoNumber,
tblCurrent.TaxYear,
tblCurrent.TaxType,
tblCurrent.FEIN,
tblCurrent.CompanyName,
-(TotalAmount) AS Amount,
tblCurrent.AccountID,
tblCurrent.ParticipantInfo,
'Refund' AS FileName,
GetDate() & ' Recd ' & [ParticipantInfo] AS Comment,
'No' AS RefundToBeProcessed,
tblCurrent.Payee,
tblCurrent.Address1,
tblCurrent.Address2,
tblCurrent.Address3,
tblCurrent.Department,
tblCurrent.ToSSCRTT
FROM tblCurrent
WHERE (((tblCurrent.AutoNumber) Between
([forms]![frmTaxUnitMain]![txtAutonumberFrom]) And
([forms]![frmTaxUnitMain]![txtAutonumberTo])) AND ((tblCurrent.Archived)=-1));

ERROR: Inccorect syntax near '!' in 'Where' Clause

**************************************

UPDATE tblCheckNumber
INNER JOIN tblHistory
ON tblCheckNumber.Autonumber = tblHistory.AutoNumber
SET tblHistory.CheckAmount = ([tblchecknumber].[amount1]),
tblHistory.CheckNumber = [tblchecknumber].[checknumber],
tblHistory.CheckDate = [tblchecknumber].[checkdate],
tblHistory.AccountNumber = [tblchecknumber].[AccountNumber],
tblCheckNumber.Updated = "YES"
WHERE (((tblHistory.CheckAmount) Is Null Or (tblHistory.CheckAmount)=0)
AND ((tblHistory.CheckNumber) Is Null) AND
((tblHistory.CheckDate) Is Null) AND ((tblHistory.AccountNumber) Is Null));

ERROR: Incorrect syntax near the keyword 'INNER'. Incorrect syntax near
'tblHistory'.

******************************************

 

[Jeff Boyce]

Migration/conversion to what?

The first SQL statement includes references to form controls. Are the forms
referenced actually open? If the conversion was to SQL-Server, and the
query is now, say, a view, SQL-Server doesn't know how to look inside Access
to find forms.

The second SQL statement might have a missing space, but I can't tell. What
I can tell is that you are joining two tables together on fields named
"Autonumber". If these are truly Access autonumber fields, you CAN'T DO
THIS! I mean, yes, you can do it, but it makes no sense to do so.
Autonumbers are automatically-generated row identifiers, and the autonumbers
in Table1 have NO RELATIONSHIP to the autonumbers that get generated for
Table2.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/