Recent content by Micha3lS

Alright. I will go with that approach. Thank you all for your help!

Yes I could use the filename or even the production date as check to prevent duplicates. Would you create a seperate table to save, which files or dates have been imported already? However I want to have the possiblity (maybe via user dialogue choice) to update the data. I guess to accomplish...

Good morning, thanks for all your replies. I didn't have time to answer on the weekend. Just to calrify: The code for the data import runs in access. The function however is called in the excel sheet with a button press, so the enduser does not have to interact with excel at all. We want...

Isn't that what I'm doing at the moment? I import into the table Grunddaten, call the function to calculate the shift, then INSERT INTO the production data tabel and I delete the Grunddaten table afterwards, to be ready for the next import. Or am I not understanding what you mean?

Since I have to use excel as an intermediate step (what I really don't like, but that what it is at the moment) I could try to generate a number here, to atleast identify the records, delete them and import them from new. I will ask about the autonumber from SAP, but I highly guess there is...

It's 10.000 maybe 20.000 records max for each import. I added the indexes and tried again nothing changed. The hwole process includes importing the data from an excel file (7000 rows), calculating shift date and shiftID, then adding the result to the ProductionData table...

This is the production data after import. The Schichtdatum (date, which might change with the import) and SchichtID are calculated during import. Also the Gutmenge and Ausschuss are grouped by the materialnummer as smalles value. The data is imported into the table Grunddaten and then...

Hello all, I'm getting excel data from production (from an SAP export) and also manual entries for the day each day. At the moment I call a access function from the excel workbook that executes the import. For the manual data I can stop duplicates by using an index, for the production however I...

Thank you very much vor your help. I got it working, although looking really ugly and Using 3 querys: If anybody is interested and finds a similar problem (Note the day starts with a night shift, e.g. from 22:00 the previous day) Public Function getShiftID(shiftType As Integer, datex As Date...

Yeah thats correct, that was another Typo, it should be strSQL, but didnt matter since it was never called anyway. IsNull(rs.Fields(0).Value) Did work for me now

You are absolutely correct. That seems to be the provlem. However I tired to change it to If rs.eof then or If rs.recordCount = 0 But the resultset always contains atleast one entry, sometime a value, sometimes Null. Also I can not access this valuev with rs!ID. This returns error 3265...

I also tried using datediff: SELECT Max(ID) FROM Shifts WHERE ShiftType = 3 AND Weekdaynumber = 2 AND Werk = 2101 AND Datediff('n',Start, #13:08:14#) < 0; And SELECT Max(ID) FROM Shifts WHERE ShiftType = 3 AND Weekdaynumber = 2 AND Werk = 2101 AND Datediff('n',TimeValue([Start]), #13:08:14#) <...

Didn't make any difference unfortunately, still no result.

Hello all, I'm trying to compare a time in date/time format to another time and check if ist bigger. The time comes from a query in which one column calls the function. The Function looks like this: Public Function getShifDate(shiftType As Integer, datex As Date, timex As Date, loc As...