excel date formula not working in access (1 Viewer)

[Randy]

I have an excel formula that I need to work in Access. I was thinking it was a straight copy, but must need slightly different formula in Access.

so in excel I have this
cell A1 Cell B1
GS1A1426 62014

Formula in B1
=MONTH(MID(A1,7,2)*7+DATE(MID(A1,5,2),1,1)-2)&"20"&MID(A1,5,2)
So this formula, just reads A1 and positions 5,6,7,& 8 represent the week/year that this item was produced. It then converts it into a month year for reporting purposes. So in this example GS1A1426 = Year 2014 week 26 and converts to 62014.



I change the formula to this in MS Access
productionmonth:MONTH(MID([tblifsreceiptsdetailreport]![serial number],7,2)*7+DATE(MID([tblifsreceiptsdetailreport]![serial number],5,2),1,1)-2)&"20"&MID([tblifsreceiptsdetailreport]![serial number],5,2)

Basically all I did was replace A1 in the formula with field name
[tblifsreceiptsdetailreport]![serial number]. In the access data base field
[tblifsreceiptsdetailreport]![serial number] = GS1A1426

I thought this might work, but it does not. Can anyone explain what I am doing wrong?

 

[Geotch]

Look at this query, multiple steps but gets the month you need. I'm not sure using the 7*week will work out perfectly.

 

[namliam]

Converting weeknumbers properly into dates isnt as easy as it seems, unless you are defining 1/1 of any year to be "week 1" which in most known date systems on the globe will not be true , you have your hardcoded -2 behind the date function but that wont be the case for al years.

For starters, DATE is a reserved word in Access where it isnt in Excel.
Instead you want to use DateSerial as a replacement

test = "GS1A1426"
?MONTH(MID(test,7,2)*7+DateSerial(MID(test,5,2),1,1)-2)&"20"&MID(test,5,2)
62014

 

[Randy]

that worked just fine. For what we are doing, the first week can be 1/1/XXXX. I understand for many, Week 1 REAL start date is important, but for this project week 532013 vs 12014 is not an issue. We are doing a rolling 26 week average, so it will not affect the numbers. Again many thanks for all the help.

 

[namliam]

As long as it works for you , just making sure you are aware of the issues

 

[Randy]

ok an "issue" since using this formula

this formula was used in a simple select query. No criteria, or grouping etc. when I run the query it gives me a correct answer set. I have an excel pivot table connected to the query.

This formula is also in the query
repairtime: DateDiff("d",[tblifsreceiptsdetailreport]![receivedate],[tblosakarepairreport]![repair date])


when I add the ProdDate formula (from this thread), the pivot table will not function. If I remove the ProdDate formula, the pivot table functions normally.

other than the two formulas it is simple 6 column query.
Ifsrmaid
serial number
SiteName
ReturnModel
Model In
Model Out
repairtime (formula that does not create the issue)
prodmonth (formula in this thread from above that stops the pivot table from working)


error message is "Data could not be retrieved from the database" then click on OK and get this "Problems Obtaining Data"

Both the excel workbook, and the MS access application are on my hard drive in the same folder.

Anyone seen this before?

 

[namliam]

Doesnt ring a bell, then again, i usually dont actively link an excel sheet to a db

 

[Randy]

yea, 99% of our efforts are taking data from two legacy ERP systems, loading the data into MS Access, creating simple queries that have some formulas and roll ups, then having the financial end user community link excel pivot tables to the predefined queries. I have 6 major queries that about 30 members link to, and this is the only one not working. very perplexing.