Getting the date sliced..

[DeV]

I'm having to import data from a .csv file and that finally works. The csv files have a date in them and I only need the month. Editing the csv files is not an option on that though. I have read some stuff about a DatePart function but that one just doesn't seem to work for me.

SELECT COUNT([PointTag]) AS Amount, [EventDate], [MessageStr], [PointTag], [PointDesc] 
INTO EventJournal
FROM Events
GROUP BY [EventDate],[MessageStr], [PointTag], [PointDesc]
ORDER BY COUNT([PointTag]) DESC;

The EventDate is the field that I want sliced and basically the only thing I want to import is the month number instead of the full date. Does anyone know what I can do to get this working? The dates are in mm-dd-yyyy format if that can help.

Thnx in advance,

DeV

 

[Mile-O]

Import the CSV file as it is. Base a query on the imported table and don't select the EventDate field. Add a new field to the query called EventMonth with the expression:

EventMonth: Month([EventDate])

 

[DeV]

Thanks that helps, only problem is that it currently slices the day instead of the month. Guess I should just put Day instead of Month? I also noticed that some records in the table have a different structure.

Normal: mm-dd-yyyy
Some records: dd-mm-yyyy

Anything to do about that automatically?

EDIT:
I was also wondering if there would be a way to reduce my queries. I currently have an awfull lot of simple ones and I'd love to combine them in one. Would it be possible to combine e.g.

UPDATE EventJournal SET AreaCode = '6000/2'
WHERE PointTag Like '2-U/*';

UPDATE EventJournal SET AreaCode = '6000/3'
WHERE PointTag LIKE '3-U/*';

UPDATE EventJournal SET AreaCode = '6000/4'
WHERE PointTag LIKE '*-UNIT';

UPDATE EventJournal SET AreaCode = 'Pilot'
WHERE PointTag LIKE '9*';

 

[Pat Hartman]

Anything to do about that automatically?

- No. You'll need to identify the improperly formatted dates PRIOR to importing them and fix the .csv file. Do some of your users have their Windows country codes improperly set?

I was also wondering if there would be a way to reduce my queries. I currently have an awfull lot of simple ones and I'd love to combine them in one. Would it be possible to combine e.g.

If you weren't using LIKE, you could create a table of old values and new values. You could then join to the table on OldValue and update to NewValue. Another alternative is to create a function. You could then use one query.

UPDATE EventJournal SET AreaCode = MyFunc(PointTag);

Use a Case statement to determine the AreaCode value for each PointTag.

Public Function MyFunc(PointTag) As Variant
Dim AreaCode As String

Select Case PointTag 
    Case Like '2-U/*'
        AreaCode = '6000/2'
    Case LIKE '3-U/*'
        AreaCode = '6000/3'
    Case LIKE '*-UNIT'
        AreaCode = '6000/4'
    Case LIKE '9*'
        AreaCode = 'Pilot'
    Case Else
        AreaCode = 'Unknown'
End Select
MyFunc = AreaCode
End Function

 

[DeV]

- No. You'll need to identify the improperly formatted dates PRIOR to importing them and fix the .csv file. Do some of your users have their Windows country codes improperly set?

Wouldnt it be possible to say something like:

If Month(EventDate) > 12 Then
        EventMonth = Day(EventDate)
Else
        EventMonth = Month(EventDate)

If so, how could I implement this?

 

[Pat Hartman]

If the dates are loaded into a date field, Access will "interpret" them and as long as it can make sense out of the date, it will store it properly. For example 13/12/04 would be interpreted as 13-dec-04 regardless of the windows country settings but 11/12/04 would be interpreted as 12-nov-04 if the settings were US but as 11-dec-04 if the settings were UK. Dates are stored as double precision numbers. They are NOT stored as strings. So, Month(YourDate) would NEVER return a number outside the range of 1-12.

 

[DeV]

To be quite honest, the command Month, returns the day. I have a file that has dates from 10th of Januari till the 13th of Januari. Only problem is that the 10th untill the 12th are in mm-dd-yyyy and the 13th is in dd-mm-yyyy. Now you say that it doesnt matter what format it is because it still returns the proper month. But when I check the table it says that the month is 10-12 and the only one that he gets right is when the date is in dd-mm-yyyy.

When i call the Day statement I get the proper month for Januari 10th-12th but for Januari 13th it returns the month 13. Probably because I used the Day statement, so it wont notice that it shouldnt be higher because days can go up to 31. I really don't feel like editing a 20.000+ database to get the dates right, aint there some sort of procedure (maybe in VB) that you can run to get the dates in 1 format?

Hope you understand what I'm trying to say here.

 

[DeV]

- No. You'll need to identify the improperly formatted dates PRIOR to importing them and fix the .csv file. Do some of your users have their Windows country codes improperly set?

If you weren't using LIKE, you could create a table of old values and new values. You could then join to the table on OldValue and update to NewValue. Another alternative is to create a function. You could then use one query.

UPDATE EventJournal SET AreaCode = MyFunc(PointTag);

Use a Case statement to determine the AreaCode value for each PointTag.

Public Function MyFunc(PointTag) As Variant
Dim AreaCode As String
Select Case PointTag 
    Case Like '2-U/*'
        AreaCode = '6000/2'
    Case LIKE '3-U/*'
        AreaCode = '6000/3'
    Case LIKE '*-UNIT'
        AreaCode = '6000/4'
    Case LIKE '9*'
        AreaCode = 'Pilot'
    Case Else
        AreaCode = 'Algemeen'
End Select
MyFunc = AreaCode
End Function

Could you explain that piece of code to me please? I've been trying it but the Case Like is not accepted by VB. If i just drop the Like i get an error when I run the query stating that it cant find my function. Appreciate the help.

DeV

 

[Pat Hartman]

Sorry, I misread the help entry for the Select Case. You cannot use Like so the case statement is not an option.

To be quite honest, the command Month, returns the day.

- No, the Month() function returns the MONTH. Access isn't prescient. When you put bad data in, you get bad data out.

aint there some sort of procedure (maybe in VB) that you can run to get the dates in 1 format?

The dates ARE in the same format. The problem is that your input was inconsistant. I explained how Access would attempt to interpret inconsistant data. It has NO way of determining that you mean 2/1/5 to be 2-Jan rather than 1-Feb except by how the language settings said to interpret the date.

If you are certain that you can identify the "bad" dates, you can write an update query that selects those dates (or date range) and updates them.

Update YourTable Set YourDate = CDate(Day(YourDate) & "/" & Month(YourDate) & "/" & Year(YourDate))
Where YourDate = #1/13/5#;

 

[DeV]

Like I said I have nothing to do with the data that comes into my database.
It is being imported from csv files that are stored in a folder on the network.
These csv files are generated every hour by all the apparatus connected to those networks.
So either the entire network is crooked or Access just imports them the wrong way.

Anyway I'll just try if I can remake those dates into a better format.

 

[Pat Hartman]

So either the entire network is crooked or Access just imports them the wrong way.

Access imports them based on your country settings. If the data is not being imported properly, there is an inconsistancy between how the data actually exists in the csv file an what you are telling Access to use as a standard date format.

Try making an import spec for the csv file. You can click on the advanced button where you can specify EXACTLY how the date is formatted in the input file. Access will take it from there. There is absolutely nothing you can do if the input data is not consistantly formatted. It must be in month, day, year, or day, month, year or some other CONSISTANT field order.