How to generate next date if not available in table

[Ashfaque]

Hi,
One of column of table contains dates of the month. This data with some other columns, we are exporting to a pre-designed formatted Excel sheet.
01/08/2016
02/08/2016
03/08/2016
04/08/2016
05/08/2016
08/08/2016
09/08/2016
….
….
30/08/2016
31/08/2016

My question is : if some dates missed / are not available (with complete record set) in the access tbl but while exporting Excel, we want to check them if next date after current record is no available, it should generate following date adding 1 day to current record set date.

Example.

After 05/08/2016 there are 2 dates not available in table and the next available date is 08/08/2016.

In this case, we would like to generate 06/08/2016 and 07/08/2016 and export them to excel sheet. The same situation arise with some other date. The purpose is to export all dates data from 1st to 31st or till end of the month even if there are less dates available in the table.

different Code lines to open excel application here……
….
…

Do While Not rst.EOF

Here some other code lines of IF THEN
….

IN between of my lengthy code I tried to check thru below code lines if I could generate the date that is not available in the table but it is not working. I don’t know if the trick is the right way to do it.
Code Tags Added by UG
Please use Code Tags when posting VBA Code
https://www.access-programmers.co.u...e-use-code-tags-when-posting-vba-code.240420/

    Dim AdvDate, CurrentDate As Date
    CurrentDate = Format(Left(rst![f.time], 10))
    AdvDate = DateAdd("d", 1, TempDate)
    rst.MoveNext
    If Format(Left(rst![f.time], 10)) = AdvDate Then
    'CurrentDate = rst![f.time]
    'rst.MovePrevious
    MsgBox "Next date available"
    Else
    MsgBox "no next date"
    End If

Other code lines here…

Rst.MoveNext
Loop

Please note f.time is a lenthy format like 03/08/2016 07:54:00 AM. Therefore we shortened it to compare only.

Any help shall be appreciated...

Thnaks,

 

[JHB]

Compare the previous date (store it in a variable) with the current date, (use DateDiff), if the distance between them is more a one, then use a loop to fill in the missing date(s) in the Excel sheet.

 

[Ashfaque]

Thanks JHB,

I did the same
Code Tags Added by UG
Please use Code Tags when posting VBA Code
https://www.access-programmers.co.u...e-use-code-tags-when-posting-vba-code.240420/

   Dim AdvDate, CurrentDate As Date
    CurrentDate = Format(Left(rst![f.time], 10))
    AdvDate = DateAdd("d", 1, TempDate)
    rst.MoveNext
    DummyDate = Format(Left(rst![f.time], 10))
    If DummyDate > AdvDate Then
    rst.MovePrevious
    ....
    .....

It is generating as desired But:

1. How about when more than 1 date is not available ?
2. May be in some months more than or 3 dates data is not available in the column. In this case how it would generate?
3. It should not compare after the last date of the month.

I think it requires a counter check / loop as you suggested in between if there are more than 1 non-availbility of next date the .....do something...

Wiered for me. Please extend your help.

Thanks in advance..

 

[Gasman]

Could you not find the difference in dates in days, and then loop that many times?

So in your example 08/085/16 - 05/08/16 = 3. Take off 1 for the number of missing days. If that is > 1 then do the loop.

In this case loop twice adding loop count to the lower date?

Which is pretty much what JHB was advising.

 

[JHB]

Thanks JHB,

I did the same

Actually I can't see you're doing that, show me where you calculate the difference between the 2 dates?
Show the whole code you've, or even better post your database, (zip it), if you still can't get it after reading Gasman post.

 

[Ashfaque]

Thanks Gentlmen,

I have attached my db herewith for you to review.

May be you will find my code lengthy and some lines there repeatative...

Please remove few record sets and check.

Thanks for your help in advance

 

[JHB]

I've made a procedure/Sub which only put in the missing dates and the available dates in the Excel sheet, all the other stuff I've left out, database attached
I have looked at your code and will in this context mention some things as erroneous or directly wrong:
When you declare variables, each variable must be declared with its type, else the type would be variant:

Dim RowCount, AMCount, [B]PMCount As Double[/B]

Only PMCount in the above line would be of the type Double, the rest would be Variant.
If you want to strip out only the date from a Date/Time fieldtype then use the function DateValue, otherwise you can expect to get an unpredictable result if you want to compare the below TempDate with a real date, then TempDate is converted to string type in the below line.

TempDate = Left(Format(rst![f.time], "dd/mm/yyyy"), 10)

Correct is:

TempDate = DateValue(rst![f.time])

And the same same applies to time values use the function TimeValue.
Labels should only be used in connection with error handling, else is it bad programming style, spaghetti programming was it called in the past.

            GoTo CheckPoint2
            Else
            GoTo CheckPoint

"Exit Sub" in the middle of the code, is in some way the same as above, bad programming style.

 

[Ashfaque]

Thanks JHB,

I will try to adjust your code lines into my module and let you know if I could reach to success.

Thanks,

 

[JHB]

I think it is better to adjust your code to the module I gave you.

 

[Ashfaque]

You are right....I was also thinking same...

 

[Ashfaque]

Hi,

Thanks JHB,

I have added my code lines into the module you gave me. It works fine but the dates are accurately appeared in table.

Since we are receiving data into pdf file and then we are converting into excel sheet at initial stage, the first 12 days dates are appearing mm/dd/yyyy format and rest into dd/mm/yyyy format.

herefore, our module Insertdate() is producing repeating record set data. This become not the problem.

If the table data (dates) are in correct manner (dd/mm/yyyy) then there is no problem. Means the producing till it reaches to month factor in the dates.

I have attached tbl pic + out put pic it shall be easy to understand.

Each month data I am importing my table, first 12 days are appearing with American date style and rest with British...

How would I logically explain in code to consider these first 12 dates (if available all as sometime there may be less punch in / outs) are American and after 12 days they are in British.

Otherwise I will have no choice except to manually correct first 12 days in table and this will be for every employee.

Is there any idea to sort it out this issue.

Thanks in Advance...

 

[Gasman]

It sounds to me that it is only assuming American format as that is possible within the dates, up to 12 at least. When you have 13 it cannot possible be an American date.
If you convert the date in excel to a number, what date does it actually represent?

 

[Ashfaque]

Hi,

Thanks Gasman for the reply.

I have attached db to review as may be I can explained it properly. You can run the db and see the result is going beyond the 31. Please try delete record from tbl and then import it again and then export it to see the result.

Thanks in advance....

 

[JHB]

To avoid MS Access automatic and incorrect date conversion you have to create your own Import specification.
The data is imported into a temporary table, then run a query that inserts data in the table T_JeddahTS in the correct date format.
The Import specification is used instead of the DoCmd.TransferSpreadsheet.
To create an Import specification, click the "Excel" icon under the "Import & Link" group in the "External Data" tab. Choose the "Import the source data into a new table .." - set the datatype for the "Time" column to Text. Before the wizard close, mark the "Save import steps", call it "Import-Data".
Create a new query like the below:

INSERT INTO T_JeddahTS ( [AC-No], [No], Name, [Time], State, [New State], [Exception], Operation )
SELECT [AC-No], [No], Name, CDate(Format([Time],"mm\/dd\/yyyy hh:nn")) AS Expr1, State, [New State], Exception, Operation
FROM [page 1]
ORDER BY [AC-No], Name, CDate(Format([Time],"mm\/dd\/yyyy hh:nn"));

Save the query, call it "qryImportedData".
Replace the DoCmd.TransferSpreadsheet code line with the below:

    DoCmd.RunSavedImportExport "Import-Data"
    DoCmd.OpenQuery "qryImportedData"

 

[Ashfaque]

Thanks a ton JHB.

Let me go thru it and I will let you know if I could run it with all time sheets.

Thanks again.

 

[Ashfaque]

Oh.....again same issue. When I import data from other sheet for another employee, it producing same..I mean generating more than a month data....

 

[JHB]

I've attached a database + 2 Excel files - try import it, (click the "Import" button on the main form).
Did the date show correct for the 2 dataset?
If yes, could you then post a Excel file which produce wrong dates.
If not, then show a printscreen of the import result.

 

[Ashfaque]

Hi JHB,

I tried to import it using btn as you said and it produced attached debug (see pic)

Then I imported it using Access process - I mean from get external data-Excel etc and saved as page 1. Thereafter I used your query to import it into T_JeddahTS table and then exported it. (Ahtesham excel file) and it is repeatative data representation.

Your 2 Excel files are being imported fine but thru Get External Data process not thru the button.

I suspect this is because of only dates format in Excel. Your format is / / / and the file is not resulting properly is having - - - used in dates

I've attached 2 excel file that are not producing the result I wanted.

Thanks for your kind help.

 

[JHB]

...
I suspect this is because of only dates format in Excel. Your format is / / / and the file is not resulting properly is having - - - used in dates
.

It is not my format - it is example data your provide in the other threads, so do you get data both formatted with / and -?

 

[Ashfaque]

No, It was just a thought. I checked with / and - (hyphen) both. But still no success.

Please try with both excel sheet I sent you in my earlier post.