Help with selecting and replacing columns that are null Where Date is over 90d ago (1 Viewer)

Abhorsen

New member
Local time
Today, 17:48
Joined
Sep 17, 2021
Messages
9
Hey guys,

I am using PowerShell to parse a massive CSV with the ACE OLEDB driver as its far more efficient than directly importing the CSV and then modifying it as needed. The imported columns are Facility, User, NTUserID, UPN, Last Accessed Date, and Last Enable Date. As this is how the spreadsheet comes to me, I do not have a choice in spaces in the headers.

My query is as follows:

Select [Facility], [User], [NTUserID], [UPN], IIf(IsNull([Last Accessed Date]), (DATE() -365), [Last Accessed Date]) AS [Last Accessed Date], IIf(IsNull([Last Enable Date]), (DATE() -365), [Last Enable Date]) AS [Last Enable Date] from csv WHERE [Last Enable Date] < (DATE() -90) AND [Last Accessed Date] < (DATE() -90)

Now when I run that, the people that had null values for Last Accessed Date are not included in the query, even though they should be as I am selecting them with a date of a year ago from today if they have a null value.

Now I thought that it wasn't replacing the null value correctly, so I ran just the following:

Select [Facility], [User], [NTUserID], [UPN], IIf(IsNull([Last Accessed Date]), (DATE() -365), [Last Accessed Date]) AS [Last Accessed Date], IIf(IsNull([Last Enable Date]), (DATE() -365), [Last Enable Date]) AS [Last Enable Date] from csv

Without the where statement, the exported csv from that query does contain the user with the correctly replaced date if they have a null value. Then when I re-add in the WHERE clause from above, the people who had null values are missing from the output, but the original people with dates in the CSV who meet the criteria are still included in the output.

Can anyone chime in here as to what is going on?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 15:48
Joined
Aug 30, 2003
Messages
36,118
I don't think you can use the alias name in the WHERE clause, try putting the formula there. Just noticed that the alias is the same as the field. Your WHERE clause is using the field, not the calculated value.
 

Abhorsen

New member
Local time
Today, 17:48
Joined
Sep 17, 2021
Messages
9
I don't think you can use the alias name in the WHERE clause, try putting the formula there. Just noticed that the alias is the same as the field. Your WHERE clause is using the field, not the calculated value.

I thought of something similar and tried the following:

Select [Facility], [User], [NTUserID], [UPN], IIf(IsNull([Last Accessed Date]), (DATE() -365), [Last Accessed Date]) AS `Last Accessed Date`, IIf(IsNull([Last Enable Date]), (DATE() -365), [Last Enable Date]) AS `Last Enable Date` from csv WHERE `Last Enable Date` < (DATE() -90) AND `Last Accessed Date` < (DATE() -90) and got completely zero output with the AS names being used in the WHERE clause if that's what you mean.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 15:48
Joined
Aug 30, 2003
Messages
36,118
Try

Select [Facility], [User], [NTUserID], [UPN], IIf(IsNull([Last Accessed Date]), (DATE() -365), [Last Accessed Date]) AS [Last Accessed Date], IIf(IsNull([Last Enable Date]), (DATE() -365), [Last Enable Date]) AS [Last Enable Date] from csv WHERE IIf(IsNull([Last Enable Date]), (DATE() -365), [Last Enable Date]) < (DATE() -90) AND IIf(IsNull([Last Accessed Date]), (DATE() -365), [Last Accessed Date]) < (DATE() -90)
 

Abhorsen

New member
Local time
Today, 17:48
Joined
Sep 17, 2021
Messages
9
Try

Select [Facility], [User], [NTUserID], [UPN], IIf(IsNull([Last Accessed Date]), (DATE() -365), [Last Accessed Date]) AS [Last Accessed Date], IIf(IsNull([Last Enable Date]), (DATE() -365), [Last Enable Date]) AS [Last Enable Date] from csv WHERE IIf(IsNull([Last Enable Date]), (DATE() -365), [Last Enable Date]) < (DATE() -90) AND IIf(IsNull([Last Accessed Date]), (DATE() -365), [Last Accessed Date]) < (DATE() -90)

Holy shit looks like that worked, I don't know how I didn't think of that! Thanks so much mate. Only thing is the dates are outputting as mm/dd/yyyy 0:00 and once I figure out how to get those to be just mm/dd/yyyy I'll be golden. Thanks again for the help.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:48
Joined
Feb 28, 2001
Messages
27,001
When you use apostrophe, as WHERE `Last Enable Date` < (DATE() -90) you confused Access because that is a literal text string, not a reference to a field name. Try [] as the field specifier in the WHERE clause and also for Last Accessed date.

Also, do you really want the AND for the date comparisons? Should that really be an OR?
 

Abhorsen

New member
Local time
Today, 17:48
Joined
Sep 17, 2021
Messages
9
When you use apostrophe, as WHERE `Last Enable Date` < (DATE() -90) you confused Access because that is a literal text string, not a reference to a field name. Try [] as the field specifier in the WHERE clause and also for Last Accessed date.

Also, do you really want the AND for the date comparisons? Should that really be an OR?

The reply above helped solve the issue. And it should be an AND, at least I believe so; I am trying to get a list of users who have not accessed the program in the past 90 days and who have not been enabled in the past 90 days so that access can be revoked. Essentially if they were just enabled or if they have recently used the program then they keep access, but if not then its revoked.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 15:48
Joined
Aug 30, 2003
Messages
36,118
Holy shit looks like that worked, I don't know how I didn't think of that! Thanks so much mate. Only thing is the dates are outputting as mm/dd/yyyy 0:00 and once I figure out how to get those to be just mm/dd/yyyy I'll be golden. Thanks again for the help.

Happy to help and welcome to the site by the way!

You can either use the Format() function on the date outputs or format the textboxes on forms/reports.
 

Abhorsen

New member
Local time
Today, 17:48
Joined
Sep 17, 2021
Messages
9
Happy to help and welcome to the site by the way!

You can either use the Format() function on the date outputs or format the textboxes on forms/reports.

Thanks for that! Looks like an OLEDB driver is far superior to PowerShell in pulling information out of large files so I guess ill be using Access query for the foreseeable future; I'll be around quite a bit from what I can see ha.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:48
Joined
Feb 28, 2001
Messages
27,001
OK, you really DO want the AND, then. Had to ask, but your reason is good!
 

Abhorsen

New member
Local time
Today, 17:48
Joined
Sep 17, 2021
Messages
9
One more question for you guys; Is it possible to check a file for usernames with an access query in PowerShell? I wanted to do something like:

Code:
$enforceList = (compare-object $enforceUsers $dblist -IncludeEqual -ExcludeDifferent).inputobject

$enforceTable = Invoke-CsvSqlcmd -csv "C:\temp\Filtered.csv" -sql "SELECT * from csv WHERE [NTUserID] IN $enforceList" -FirstRowColumnNames

Now enforceList is just an array containing usernames; i.e if I export it to a txt file its something like:

User1
User2
User3
User4

Is there something I need to do to $enforceList before being able to use it in a WHERE IN clause like above? Something like add a ' to before and after every User, as well as a comma after that so its like below?

'User1',
'User2',
'User3',
'User4'
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 15:48
Joined
Aug 30, 2003
Messages
36,118
I have no PowerShell experience, but the proper syntax for IN includes parentheses. In Access you'd also have to concatenate the value into the string, so something like:

"SELECT...WHERE [NTUserID] IN(" & $enforceList & ")"
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 15:48
Joined
Aug 30, 2003
Messages
36,118
Oh, and for text values you would need the single quotes around each value.
 

Abhorsen

New member
Local time
Today, 17:48
Joined
Sep 17, 2021
Messages
9
To post an update for anyone else in the same situation (Running MS Access SQL query from within PowerShell), the SQL query string that worked for me was exactly as below:

"SELECT * from csv WHERE [NTUserID] IN( $enforceList )"

Where $enforceList was a single string (NOT an array object) containing a list with single quotes and delimited by commas; i.e:

'User1', 'User2', 'User3', 'User4'
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 15:48
Joined
Aug 30, 2003
Messages
36,118
Thanks for updating the thread with your solution. I keep meaning to try PowerShell, then I keep getting distracted by pesky work projects. :p
 

Users who are viewing this thread

Top Bottom