Changing Dynamic field to Static Nest (1 Viewer)

P

Pearl1

Registered User.
Local time
Yesterday, 17:36
Joined
Jul 17, 2014
Messages
31
Hi,

I've got a spreadsheet link to access (through ODBC). I've written a query...[SOH]+[2/03/2020](Current day)=T1 which gives me a result (so far all good). I will refresh the linked spreadsheet the next day, now the date is 3/3/2020(Current Day).

Currently I'm going to the query and change the date to reflect the date.

Question 1. is there way to change the date to something like DAY1 (as I've 8 days of data to look at) and each date to represent day instead of date. Below is the data...hope I'm making sense.

I was not able to attach the excel spreadsheet? Need help in this too.


Item numberSOHPUNCHED2/03/20203/03/20204/03/20205/03/20206/03/20207/03/20208/03/20209/03/2020
7005-003-01431399-10-275-231-151-119-39-41-85
7005-007-01297237-25-275-231-151-119-39-41-85
7005-009-016454-75-275-231-151-119-39-41-85
7005-010-011818-375-275-231-151-119-39-41-85
7005-052-01297237-375-275-231-151-119-39-41-85
7005-065-011461230-275-231-151-119-39-41-85
7005-066-0161610-275-231-151-119-39-41-85
7005-067-016353-3750-231-151-119-39-41-85
7005-068-011818-3750-231-151-119-39-41-85
7005-069-011616-375-275-231-151-119-39-41-85
7005-072-011818-375-275-231-151-119-39-41-85
7005-079-018456-375-275-231-151-119-39-41-85
7005-089-01260241000-151-119-39-41-85
7005-090-01128120-375-275-231-151-119-39-41-85
7005-091-01136128-375-275-231-151-119-39-41-85
7005-114-0166-375-275-231-151-119-39-41-85
7005-124-011212-375-275-231-151-119-39-41-85
7005-125-011111-375-275-231-151-119-39-41-85
7005-126-011313-375-275-231-151-119-39-41-85
7005-150-010000000000
7005-215-010000000000
7005-227-012929-375-275-231-151-119-39-41-85
7005-228-015850-375-275-231-151-119-39-41-85


Kind Regards
 
cheekybuddha

cheekybuddha

AWF VIP
Local time
Today, 01:36
Joined
Jul 21, 2014
Messages
2,277
Hi,

You could do this with code, using a recordset - but not with a straight query.

You will need to query the fields collection to get the name of the column you need.

Create a stored query that you can adjust for the required day. Call it 'qryByDay' or something more meaningful

So, if your linked table is called tblExcel you can do something like:
Code: 
  Dim rs As DAO.Recordset, iDay As Integer, strSQL As String, strDayField As String
  Const DAYS_START_COL As Integer = 3, _
        QRY_BY_DAY As String = "qryByDay"

  iDay = 1  ' 2/03/2020
  strSQL = "SELECT * FROM tblExcel WHERE 1 = 0;"    ' retrieve an empty recordset
  With CurrentDb
    With .OpenRecordset strSQL
      strDayField = .Fields(DAYS_START_COL + iDay - 1).Name
      .Close
    End With
    strSQL = "SELECT [Item number], [SOH]+[" & strDayField & "] AS [SOH - " & strDayField & "] FROM tblExcel;"
    With .QueryDefs(QRY_BY_DAY)
      .SQL = strSQL
    End With
  End With
  DoCmd.OpenQuery QRY_BY_DAY

hth,

d
 
P

Pearl1

Registered User.
Local time
Yesterday, 17:36
Joined
Jul 17, 2014
Messages
31
Hi d,

Hope all is well. Appreciate your time and assistance.

1st I'm not good at VBA (very,very basic knowledge...something like "Hello world") but I like to learn. Had couple of questions.

Q1. When mentioned "create a stored query"...do I need to create a query or will the VBA code do it automatically?

Q2. On the VBA code we have the following " iDay = 1 ' 2/03/2020" mentioned. When the linked data gets updated the date will change, as it is dynamic not static...do I have to come and change the date on the VBA code everytime.? Also I did not mention the date on the linked file is stored as "text" format. I was changing the field in the query to [SOH]+Clng[2/03/2020].

Q3. I'm getting a syntax error on code..."With .OpenRecordset strSQL". Please check attached file.

Code: 
Private Function Day()

Dim rs As DAO.Recordset, iDay As Integer, strSQL As String, strDayField As String

  Const DAYS_START_COL As Integer = 3, _
        QRY_BY_DAY As String = "qryByDay"

  iDay = 1  ' 2/03/2020

  strSQL = "SELECT * FROM Fold2 WHERE 1 = 0;"    ' retrieve an empty recordset

  With CurrentDb
    With .OpenRecordset strSQL
      strDayField = .Fields(DAYS_START_COL + iDay - 1).Name
      .Close
    End With

    strSQL = "SELECT [Item number], [SOH]+[" & strDayField & "] AS [SOH - " & strDayField & "] FROM Fold2;"

    With .QueryDefs(QRY_BY_DAY)
      .SQL = strSQL
    End With
  End With

  DoCmd.OpenQuery QRY_BY_DAY

End Function

Kind Regards
Pearl

EDITED by Isladogs to add code tags
 

Attachments

  • Syntax error.JPG
    Syntax error.JPG
    77.4 KB · Views: 415
Last edited by a moderator:
June7

June7

AWF VIP
Local time
Yesterday, 16:36
Joined
Mar 9, 2014
Messages
5,470
Please post code within CODE tags to retain indentation and readability.

AFAIK, DAO recordset must be opened with Set.
Code: 
strSQL = "SELECT * FROM Fold2 WHERE 1 = 0;" ' retrieve an empty recordset
With CurrentDb
    Set rs = .OpenRecordset(strSQL)
    strDayField = rs.Fields(DAYS_START_COL + iDay - 1).Name
    rs.Close
    strSQL = "SELECT [Item number], [SOH]+[" & strDayField & "] AS [SOH - " & strDayField & "] FROM Fold2;"
    With .QueryDefs(QRY_BY_DAY)
        .sql = strSQL
    End With
End With
 
P

Pearl1

Registered User.
Local time
Yesterday, 17:36
Joined
Jul 17, 2014
Messages
31
Hi June,

Thank you...figured out how to post code within Code tags to retain indentation and readability .

Had one question.

I was able to modify your suggestion slightly on " Const DAYS_START_COL As Integer = 4, _" and took off the date from "iDay = 1".

Is there a way of it picking up the columns from 3 to 10 , do the calculation and return result in different columns.

Kind Regards
Pearl



Code: 
Public Sub DAY()

Dim rs As DAO.Recordset, iDay As Integer, strSQL As String, strDayField As String
  Const DAYS_START_COL As Integer = 4, _
        QRY_BY_DAY As String = "qryByDay"

  iDay = 1
  strSQL = "SELECT * FROM Fold2 WHERE 1 = 0;" ' retrieve an empty recordset
With CurrentDb
    Set rs = .OpenRecordset(strSQL)
    strDayField = rs.Fields(DAYS_START_COL + iDay - 1).Name
    rs.Close
    strSQL = "SELECT [Item number],[PUNCHED], [SOH]+[PUNCHED]+[" & strDayField & "] AS [SOH - " & strDayField & "] FROM Fold2;"
    With .QueryDefs(QRY_BY_DAY)
        .SQL = strSQL
  End With
End With


End Sub
 
cheekybuddha

cheekybuddha

AWF VIP
Local time
Today, 01:36
Joined
Jul 21, 2014
Messages
2,277
Hi Pearl,

Let me answer your questions from post #3, then post #5

>> Q1. When mentioned "create a stored query"...do I need to create a query or will the VBA code do it automatically? <<
You need to do this. Give it a name. Then name will then be the value of the constant QRY_BY_DAY in the code. But it doesn't matter what the query is, since its SQL will be set dynamically when your code runs. So it could just be:
SELECT * FROM Fold2;

>> When the linked data gets updated the date will change, as it is dynamic not static...do I have to come and change the date on the VBA code everytime. <<
No. iDay = 1 refers to the first date column in your spreadsheet. If you wanted to reder to the second date column in your example posted (3/03/2020) you would use:
iDay = 2
The premise is that your 8 date columns start at the fourth column. This column 3 in a 0-based index. So, the constant DAYS_START_COL[icode] denotes the column at which your date columns start. it is the fourth column (or column with index = 3 in a 0-based index system).
Then when you are trying to get date/column name you check the field name of the [ICODE]DAYS_START_COL + iDay - 1 field. (again -1 to account for the 0-based index).

>> Q3. I'm getting a syntax error on code..."With .OpenRecordset strSQL". <<
Sorry, the syntax error was due to missing brackets. It sould have read:
Code: 
' ...
  With CurrentDb
    With .OpenRecordset(strSQL)
      strDayField = .Fields(DAYS_START_COL + iDay - 1).Name
' ...
I used the syntax for passing arguments to a sub rather than a function - doh! :oops:
(@june, the With .. End With construct will take care of the Set, and more importantly the Set rs = Nothing, and avoids having to declare an object variable too)

>> Is there a way of it picking up the columns from 3 to 10 , do the calculation and return result in different columns. <<
Please clarify for me, do you want to open a query showing the calculation for each date? If so, along with the original values for each date as well?

Try the code first as:
Code: 
Private Function MyDay(iDay As Integer) As Boolean

  Dim strSQL As String, strDayField As String
  Const DAYS_START_COL As Integer = 3, _
        QRY_BY_DAY As String = "qryByDay"

  strSQL = "SELECT * FROM Fold2 WHERE 1 = 0;"    ' retrieve an empty recordset
  With CurrentDb
    With .OpenRecordset(strSQL)
      strDayField = .Fields(DAYS_START_COL + iDay - 1).Name
      .Close
    End With
    strSQL = "SELECT [Item number], [SOH]+[" & strDayField & "] AS [SOH - " & strDayField & "] FROM Fold2;"
    With .QueryDefs(QRY_BY_DAY)
      .SQL = strSQL
    End With
  End With
  DoCmd.OpenQuery QRY_BY_DAY
  MyDay = (Err = 0)

End Function

Do not name your function Day() as it will clash with an inbuilt VBA function name.

You can call it:
Code: 
  Call MyDay(1)

If that works, then we can work at adjusting it to output all of the days at once.

hth,

d
 
P

Pearl1

Registered User.
Local time
Yesterday, 17:36
Joined
Jul 17, 2014
Messages
31
Hi d,

Thank you...I'm not sure what happened (my lack of knowledge) but when I copied code, It asked for Macro name, which I created. Was I meant to copy the code again in the Sub Sample ()...see attached file Copy Code again.

If I was meant to copy the code again than I got the following error..."Compile error", please check the attached file.

Now if everything was correct, the query should have opened with all the data without any calculation as standard query?

Kind Regards
Pearl
 

Attachments

  • Copy Code Again.JPG
    Copy Code Again.JPG
    105.6 KB · Views: 246
P

Pearl1

Registered User.
Local time
Yesterday, 17:36
Joined
Jul 17, 2014
Messages
31
Hi d,

Sorry, missed to answer the following question you asked earlier

>> Is there a way of it picking up the columns from 3 to 10 , do the calculation and return result in different columns. <<
Please clarify for me, do you want to open a query showing the calculation for each date? If so, along with the original values for each date as well?

Yes please.
 
cheekybuddha

cheekybuddha

AWF VIP
Local time
Today, 01:36
Joined
Jul 21, 2014
Messages
2,277
Hi Pearl,

It's not clear from your image what is causing the compile error.

The code I posted should only be placed once in your project.

I see you have saved the code in to a standard module (which is fine) but in that case you can remove the 'Private' from in front of 'Function which might help the issue.
 
E

ebs17

Well-known member
Local time
Today, 02:36
Joined
Feb 7, 2020
Messages
1,944
I've got a spreadsheet ...
Click to expand...
Is it an Excel spreadsheet?

Power Query is available in Excel from version 2010. In addition to many other functions, this offers a simple and excellent unpivot function. So you could put the link on the result of the unpivot - and you would have a normalized table where you only had to filter on the desired date.

Structured instead of actionist dynamics, that could be a way.
 
P

Pearl1

Registered User.
Local time
Yesterday, 17:36
Joined
Jul 17, 2014
Messages
31
Hi D,

Thank you. Appreciate all the assistance I'm getting.

I was not able to run the VBA code you provide but below is what I was able to run. I had to take off the following..."
(iDay As Integer) As Boolean" and it worked.

Now...how do we get results for the following.

1, I need to get the rest of days result.
2, I need to link this to the Command Button in the form.

Kind Regards
Pearl




Code: 
Private Function MyDay()

Dim rs As DAO.Recordset, iDay As Integer, strSQL As String, strDayField As String

  Const DAYS_START_COL As Integer = 4, _
        QRY_BY_DAY As String = "qryByDay"

  iDay = 1  ' 2/03/2020

 strSQL = "SELECT * FROM Fold2 WHERE 1 = 0;" ' retrieve an empty recordset
With CurrentDb
    Set rs = .OpenRecordset(strSQL)
    strDayField = rs.Fields(DAYS_START_COL + iDay - 1).Name
    rs.Close
    strSQL = "SELECT [Item number], [SOH]+[" & strDayField & "] AS [SOH - " & strDayField & "] FROM Fold2;"
    With .QueryDefs(QRY_BY_DAY)
        .SQL = strSQL
    End With
End With

 DoCmd.OpenQuery QRY_BY_DAY

End Function
 
P

Pearl1

Registered User.
Local time
Yesterday, 17:36
Joined
Jul 17, 2014
Messages
31
ebs17 said:
Is it an Excel spreadsheet?

Power Query is available in Excel from version 2010. In addition to many other functions, this offers a simple and excellent unpivot function. So you could put the link on the result of the unpivot - and you would have a normalized table where you only had to filter on the desired date.

Structured instead of actionist dynamics, that could be a way.
Click to expand...

Hi ebs17,

Appreciate the offer but I like the challenges in Access. If all fails I'm sure you will have my back.

Kind Regards
Pearl
 
cheekybuddha

cheekybuddha

AWF VIP
Local time
Today, 01:36
Joined
Jul 21, 2014
Messages
2,277
OK, to see all the fields at once you can try:
Code: 
Private Function MyDay()

  Dim iDay As Integer, strSQL As String, strDayField As String

  Const DAYS_START_COL As Integer = 4, _
        DAYS_COLS_COUNT  As Integer = 8, _
        QRY_BY_DAY As String = "qryByDay"

  strSQL = "SELECT * FROM Fold2 WHERE 1 = 0;" ' retrieve an empty recordset
  With CurrentDb
    With .OpenRecordset(strSQL)
      For iDay = 1 To DAYS_COLS_COUNT
        strDayField = rs.Fields(DAYS_START_COL + iDay - 1).Name
        strSQL = strSQL & ", [" & strDayField & "], SOH + [" & strDayField & "] AS [SOH - " & strDayField & "]"
      Next iDay
      .Close
      strSQL = "SELECT [Item number], SOH, PUNCHED" & strSQL & " FROM Fold2;"
      Debug.Print strSQL
    End With
    With .QueryDefs(QRY_BY_DAY)
      .SQL = strSQL
    End With
  End With

  DoCmd.OpenQuery QRY_BY_DAY

End Function

hth,

d
 
P

Pearl1

Registered User.
Local time
Yesterday, 17:36
Joined
Jul 17, 2014
Messages
31
Hi d,

Thank you but no luck, few items I had to re-enter not sure if you missed if or was it not meant to be there. I got an error message also, I' v attached the file.

Code: 
Private Function MyDay()

  Dim rs As DAO.Recordset, iDay As Integer, strSQL As String, strDayField As String ' entered Dim rs As DAO.Recordset

  Const DAYS_START_COL As Integer = 3, _
        DAYS_COLS_COUNT  As Integer = 8, _
        QRY_BY_DAY As String = "qryByDay"

  strSQL = "SELECT * FROM Fold2 WHERE 1 = 0;" ' retrieve an empty recordset
  With CurrentDb
    Set rs = .OpenRecordset(strSQL)  ' Replaced with with Set Rs
      For iDay = 1 To DAYS_COLS_COUNT
        strDayField = rs.Fields(DAYS_START_COL + iDay - 1).Name
        strSQL = strSQL & ", [" & strDayField & "], SOH + [" & strDayField & "] AS [SOH - " & strDayField & "]"
      Next iDay
      rs.Close ' entered rs before close
      strSQL = "SELECT [Item number], SOH, PUNCHED" & strSQL & " FROM Fold2;"
      Debug.Print strSQL
    End With
    With .QueryDefs(QRY_BY_DAY)
      .SQL = strSQL
    End With
  End With


DoCmd.OpenQuery QRY_BY_DAY

End Function
 

Attachments

  • Compile error 2-03-20.JPG
    Compile error 2-03-20.JPG
    130.9 KB · Views: 419
cheekybuddha

cheekybuddha

AWF VIP
Local time
Today, 01:36
Joined
Jul 21, 2014
Messages
2,277
Try it as I had it without replacing the the Set rs - it's not necessary

(Otherwise, remove the End With that went with the With .OpenRecordset)
 
P

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:36
Joined
Feb 19, 2002
Messages
43,266
Since you are moving your spreadsheet data into Access to analyze it, why use spreadsheet methods? If you normalize the spreadsheet on the way in, you won't be impacted by the varying column names. Each spreadsheet row will end up as 8 table rows and you will use criteria to select the date range you want to analyze.
 
P

Pearl1

Registered User.
Local time
Yesterday, 17:36
Joined
Jul 17, 2014
Messages
31
Hi D,

I've tried with your original code, but this is the error message I'm getting

"Run-time error '91':
Object variable or With block variable not set.

When I debug, the following is highlighted.

strDayField = rs.Fields(DAYS_START_COL + iDay - 1).Name

Hope you can share some light...

Kind Regards
Pearl



Code: 
rivate Function MyDay()

  Dim rs As DAO.Recordset, iDay As Integer, strSQL As String, strDayField As String

  Const DAYS_START_COL As Integer = 4, _
        DAYS_COLS_COUNT  As Integer = 8, _
        QRY_BY_DAY As String = "qryByDay"

  strSQL = "SELECT * FROM Fold2 WHERE 1 = 0;" ' retrieve an empty recordset
  With CurrentDb
    With .OpenRecordset(strSQL)
      For iDay = 1 To DAYS_COLS_COUNT
        strDayField = rs.Fields(DAYS_START_COL + iDay - 1).Name
        strSQL = strSQL & ", [" & strDayField & "], SOH + [" & strDayField & "] AS [SOH - " & strDayField & "]"
      Next iDay
      .Close
      strSQL = "SELECT [Item number], SOH, PUNCHED" & strSQL & " FROM Fold2;"
      Debug.Print strSQL
    End With
    With .QueryDefs(QRY_BY_DAY)
      .SQL = strSQL
    End With
  End With

  DoCmd.OpenQuery QRY_BY_DAY

End Function
 
cheekybuddha

cheekybuddha

AWF VIP
Local time
Today, 01:36
Joined
Jul 21, 2014
Messages
2,277
Remove the rs. It's no longer required.

Code: 
strDayField = rs.Fields(DAYS_START_COL + iDay - 1).Name
 
cheekybuddha

cheekybuddha

AWF VIP
Local time
Today, 01:36
Joined
Jul 21, 2014
Messages
2,277
It's late here. I will write tomorrow to explain how the syntax construct works.
 
P

Pearl1

Registered User.
Local time
Yesterday, 17:36
Joined
Jul 17, 2014
Messages
31
Hi d,

Take care and I'm looking forward.

Thank you, did as you suggested but got a new error message saying

"Compile error"
Sub or Function not defined.

See you tomorrow.

Kind Regards
 
You must log in or register to reply here.

Similar threads

A
Solved How to change date portion at beginning
Replies
13
Views
884
Ashfaque
A
Mike_10
"Run-time error '-2147024882 (8007000e)': Out of memory." while building ADO recordset
2
Replies
37
Views
4,930
The_Doc_Man
The_Doc_Man
S
Exporting to Excel .xlsx
Replies
8
Views
1,206
The_Doc_Man
The_Doc_Man
Uncle Gizmo
50 Ultimate Excel Tips and Tricks for 2020
Replies
9
Views
2,105
Uncle Gizmo
Uncle Gizmo
S
sdell
Replies
3
Views
1,058
pbaldy
pbaldy

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom