Running an access query with detediff and comma using powershell

0

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

windows
powershell
ms-access
scripting
asked on Stack Overflow Aug 24, 2016 by Vibhav MS • edited Jul 6, 2020 by Martijn Pieters

1 Answer

0

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).

answered on Stack Overflow Aug 24, 2016 by Chris Dent

User contributions licensed under CC BY-SA 3.0