Support NeoGAF

[liquidspeed]

Hi Gaf, I am designing a database and reporting tool for my company and I have run into a few problems while writing VBA code to update three table fields ( fields – Integer Zeros, Result, and Decimal Zeros),. My intent is simply to call the function (shown below) and have it update the fields. However, I keep running into 2 problems:

1.) How do I reference the field data that I am pulling into this program ( field- final_result – it is split into two array elements and stored in the variable Izero)

2.) How do I reference the column names/ fields that I want to display the output within ( I am attempting to do this in my last 3 lines of code:
Also what is the difference between the “.” Operator and the “!” operator ?

The Form is: frm_C_IEPA_02_EDD; The Columns are Integer Zeros, Result, and Decimal Zeros

Form_frm_C_IEPA_02_EDD.[Integer Zeros] = MCount
Form_frm_C_IEPA_02_EDD.[Result] = store
Form_frm_C_IEPA_02_EDD.[Decimal Zeros] = NCount



The entire code is below:

Function ResZeros()

\\Declare variables and arrays

Dim MCount()
Dim NCount()
Dim Izero()
Dim store() as String
Dim A as String
Dim Frac as String

\\Split the data value in final_result into two array elements via the split function. “.” Is the delimiter. The result is stored in then array Izero()

Izero() = Split([dbo_SAMPLE_ANALYSIS_RESULTS]![final_result], ".",2)

\\ take the first element of Izero and store it in A; take the second element of IZero and \\store it in Frac

A = Izero(0)
Frac = Izero(1)

\\ use Cstr function to ensure that the array elements are strings. Use the Len function \\to calculate the number of characters in the string… I don’t think Cstr is needed but \\included just to be sure

length = Len(CStr(Izero(0)))
elength = Len(CStr(Izero(1)))
first = length - 5
Sec = elength – 5

\\store 0’s in array Mcount

For i = 0 To first
MCount(i) = 0
Next

\\store 0’s in array Ncount

For q = 0 To Sec
NCount(i) = 0
Next


\\ Assign the string in A to Store(0) and the string in Frac to Store(1)

For p=0 to 1
If p = 0 then
store(p) = A
else
store(p)=Frac
End If
Next

\\Return the value of Mcount to the field Integer Zeros, NCount to field Decimal Zeros, \\and store to field Result

Form_frm_C_IEPA_02_EDD.[Integer Zeros] = MCount
Form_frm_C_IEPA_02_EDD.[Result] = store
Form_frm_C_IEPA_02_EDD.[Decimal Zeros] = NCount
End Function

 

[ColdPizza]

I'm not familiar with VBA (I work in C#) but I threw this in a code block in case it makes it easier to read for others

Function ResZeros()

\\Declare variables and arrays 

Dim MCount() 
Dim NCount() 
Dim Izero()
Dim store() as String
Dim A as String
Dim Frac as String

\\Split the data value in final_result into two array elements via the split function. “.” Is \\the delimiter. The result is stored in then array Izero()

Izero() = Split([dbo_SAMPLE_ANALYSIS_RESULTS]![final_result], ".",2) 

\\ take the first element of Izero and store it in A; take the second element of IZero and \\store it in Frac

A = Izero(0) 
Frac = Izero(1)

\\ use Cstr function to ensure that the array elements are strings. Use the Len function \\to calculate the number of characters in the string… I don’t think Cstr is needed but \\included just to be sure

length = Len(CStr(Izero(0)))
elength = Len(CStr(Izero(1)))
first = length - 5
Sec = elength – 5

\\store 0’s in array Mcount

For i = 0 To first
MCount(i) = 0
Next

\\store 0’s in array Ncount

For q = 0 To Sec
NCount(i) = 0
Next


\\ Assign the string in A to Store(0) and the string in Frac to Store(1)

For p=0 to 1
If p = 0 then
store(p) = A
else
store(p)=Frac
End If
Next

\\Return the value of Mcount to the field Integer Zeros, NCount to field Decimal Zeros, \\and store to field Result

Form_frm_C_IEPA_02_EDD.[Integer Zeros] = MCount
Form_frm_C_IEPA_02_EDD.[Result] = store
Form_frm_C_IEPA_02_EDD.[Decimal Zeros] = NCount
End Function

 

[liquidspeed]

I'm not familiar with VBA (I work in C#) but I threw this in a code block in case it makes it easier to read for others

Function ResZeros()

\\Declare variables and arrays 

Dim MCount() 
Dim NCount() 
Dim Izero()
Dim store() as String
Dim A as String
Dim Frac as String

\\Split the data value in final_result into two array elements via the split function. “.” Is \\the delimiter. The result is stored in then array Izero()

Izero() = Split([dbo_SAMPLE_ANALYSIS_RESULTS]![final_result], ".",2) 

\\ take the first element of Izero and store it in A; take the second element of IZero and \\store it in Frac

A = Izero(0) 
Frac = Izero(1)

\\ use Cstr function to ensure that the array elements are strings. Use the Len function \\to calculate the number of characters in the string… I don’t think Cstr is needed but \\included just to be sure

length = Len(CStr(Izero(0)))
elength = Len(CStr(Izero(1)))
first = length - 5
Sec = elength – 5

\\store 0’s in array Mcount

For i = 0 To first
MCount(i) = 0
Next

\\store 0’s in array Ncount

For q = 0 To Sec
NCount(i) = 0
Next


\\ Assign the string in A to Store(0) and the string in Frac to Store(1)

For p=0 to 1
If p = 0 then
store(p) = A
else
store(p)=Frac
End If
Next

\\Return the value of Mcount to the field Integer Zeros, NCount to field Decimal Zeros, \\and store to field Result

Form_frm_C_IEPA_02_EDD.[Integer Zeros] = MCount
Form_frm_C_IEPA_02_EDD.[Result] = store
Form_frm_C_IEPA_02_EDD.[Decimal Zeros] = NCount
End Function

thanks I appreciate it.

Does anyone know the appropriate syntax for referencing a field?

 

[Mr. Tone]

I'm no expert in Access VBA, but in my experience if you're referencing a field directly, you need to be doing it in the context of a recordset object, or do your manipulation via SQL queries. Here's a bit of a primer on working with recordsets. if you open your table as a recordset, you can iterate through it, doing all that manipulation one record at a time, this is where the ! Syntax is used. The . Syntax is used for referencing fields within SQL.

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("dbo_SAMPLE_ANALYSIS_RESULT")

Rs.MoveFirst
While not Rs.eof
     Lzero =   Split( Rs![final_result], ".", 2)
    // do stuff here
Rs.Movenext // move to next record
Wend

Sorry if this doesn't make sense, I'm on my iPad, but I hope this points you in the right direction.

 

[liquidspeed]

I'm no expert in Access VBA, but in my experience if you're referencing a field directly, you need to be doing it in the context of a recordset object, or do your manipulation via SQL queries. Here's a bit of a primer on working with recordsets. if you open your table as a recordset, you can iterate through it, doing all that manipulation one record at a time, this is where the ! Syntax is used. The . Syntax is used for referencing fields within SQL.

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("dbo_SAMPLE_ANALYSIS_RESULT")

Rs.MoveFirst
While not Rs.eof
     Lzero =   Split( Rs![final_result], ".", 2)
    // do stuff here
Rs.Movenext // move to next record
Wend

Sorry if this doesn't make sense, I'm on my iPad, but I hope this points you in the right direction.

Thanks for your time... This might be helpful