I am trying to run the below access query using powershell as part of a larger task.
SELECT PG.LastName+`", `"+PG.FirstName AS Name, PG.Gender, PG.Ethnicity, PG.Language, PG.PhoneNumber, PG.FinancialClass AS FinClass, PG.BirthDate, Int(DateDiff(`"d`",PG.BirthDate,Now())/365.2425) AS Age, Switch(Int(DateDiff(`"d`",PG.BirthDate,Now())/365.2425)<12,PG.GLastName,Int(DateDiff(`"d`",PG.BirthDate,Now())/365.2425)>=12,Null) AS GLastName, Switch(Int(DateDiff(`"d`",PG.BirthDate,Now())/365.2425)<12,PG.GFirstName,Int(DateDiff(`"d`",PG.BirthDate,Now())/365.2425)>=12,Null) AS GFirstName FROM TestData INNER JOIN PG ON TestData.PI = PG.PINumber WHERE (((PG.Id) Not In (734,735)))
When I run the query from access regularly, it works fine. Since I was trying to run it through powershell, I added the ` sign in front of quotes and commas but the query is still throwing the below error when I run it
IErrorInfo.GetDescription failed with E_FAIL(0x80004005)
I checked some previous threads and made the above changes but it still doesnt seem to work.
Any suggestions as to what might be wrong with the above query?
Thanks, Sreekar
I would declare it in a here-string (the example below), or a non-expandable string (single quotes, 'something') so I didn't have to worry about escape characters.
You can include PowerShell variables inside the double-quoted here-string below as normal ("SELECT $myVariable ..."
).
Excuse the formatting, it's this way so I could find all the escape characters.
e.g.
$query = @"
SELECT PG.LastName + ", " + PG.FirstName AS Name,
PG.Gender,
PG.Ethnicity,
PG.Language,
PG.PhoneNumber,
PG.FinancialClass AS FinClass,
PG.BirthDate,
Int(DateDiff("d", PG.BirthDate,Now()) / 365.2425) AS Age,
Switch(
Int(DateDiff("d", PG.BirthDate, Now()) / 365.2425) < 12, PG.GLastName,
Int(DateDiff("d", PG.BirthDate, Now()) / 365.2425) >= 12, Null
) AS GLastName,
Switch(
Int(DateDiff("d", PG.BirthDate, Now()) / 365.2425) < 12, PG.GFirstName,
Int(DateDiff("d", PG.BirthDate,Now()) / 365.2425) >= 12, Null
) AS GFirstName
FROM TestData
INNER JOIN PG ON TestData.PI = PG.PINumber
WHERE (((PG.Id) Not In (734,735)))
"@
There is also a single-quoted variant of this here-string:
@'
SELECT ...
'@
Note: When opening a here-string the start must not be followed by anything but a line-break. When closing a here-string the end cannot be preceded by anything (including white-space indentation).
User contributions licensed under CC BY-SA 3.0