Calculation in a Query

[bsdixon]

I am trying to add a formula to my query that adds three fields together. In my query I entered a new column and put SUM:[Field1]+[Field2]+[Field3]

I have already entered in a lot of the data and some of the fields are blank. The formula will not work unless there are zero's in these fields. I have now set the default value to zero so that this will not happen in the futue, but how do I get the formula to work with my current fields that have blank entries?

Thanks for your help.

 

[Pat Hartman]

If it is important to make the distinction between 0 and not entered, you need to put your default back to null and run an update query to replace any zero values with null. Nulls and zeros are handled differently by aggregate and domain functions. For example the average of 2, null, 4 is 3 whereas the average of 2, 0, 4 is 2. Only you can decide which is correct for your situation.

If you decide zero is correct, write an update query that selects all rows with null values and updates them to 0. If you decide that null is correct, write the update query to select all 0 values and change them to null. This is a little more dangerous though since 0 is a valid value and may have been entered by the user rather than defaulted to.

If you need to leave the nulls in some of the fields, then you need to account for them with the Nz() function in certain situations.

MySum:Nz([Field1],0) + Nz([Field2],0) + Nz([Field3],0)

Choose a better name than Sum since Sum is the name of an aggregate function. Do not use the names of functions as column names.

 

[bsdixon]

I want to keep the null values so I tried your null function that you posted. Unfortunately, this function does not take into account the values that have been entered. The function gave a result of zero.

Here is what I have in the field box.

Total: Nz("Field 1",0)+Nz("Field 2",0)+Nz("Field 3",0)

If I put a value in the table field the query will not work.

What am I doing wrong? Thanks for your help.

 

[Rich]

Do not put quotes around the field names but brackets as Pat posted

 

[bsdixon]

Thanks for the help. On my first try I did use the brackets and it did not work so I tried the quotation marks. The second time I tried the brackets it worked.

The only problem that I am having now is setting the number format and decimal places. Can this be done in a query?

I tried to do this in my form, but it does not change the number format when I change the setting. I am not for sure why this is happening.

Thanks for your help.

 

[bsdixon]

I have another problem with my calculation. When I export the form to excel, the caculated values show up as different numbers. In one column I get the error message #Name?

What is causing the error message and how do I get the correct values to export to excel?

Thanks for your help.

 

[bsdixon]

HELP!!

I am having yet other problem with the query. I have a button that I want to be able to click that exports the entire form to excel. I used an "OutputTo" macro.

When I click on the button, I get the error "Microsoft Access can't start the OLE server".

I do not get this error when I analyze other forms in excel.

What I am I doing wrong??

 

[Pat Hartman]

Try using TransferSpreadsheet to export the query rather than the form.

 

[bsdixon]

Thanks for the help Pat. That worked great!


Now the only problem that I am having is setting the number format and decimal places. Can this be done in a query?

I tried to do this in my form, but it does not change the number format when I change the setting. I am not for sure why this is happening.

Thanks for your help!

 

[Pat Hartman]

Change the format in the query.

 

[bsdixon]

Thanks for the help on the query.

I am having a problem with the transfer spreadsheet macro. I have multiple users so we cannot all save to the same place.

For example, I want it to save to C:\Documents and Settings\bsdixon\Desktop

bsdixon is my user ID so this would change for each user.

Does anyone have a suggestion? Is there a way that I can have it prompt to enter in the file?

 

[Mile-O]

Put this in a module:

  ' Declare for call to mpr.dll.
   Declare Function WNetGetUser Lib "mpr.dll" _
      Alias "WNetGetUserA" (ByVal lpName As String, _
      ByVal lpUserName As String, lpnLength As Long) As Long

   Const NoError = 0       'The Function call was successful


Public Function GetUserName()

      On Error GoTo Err_ErrorHandler

          ' Buffer size for the return string.
      Const lpnLength As Integer = 255

      ' Get return buffer space.
      Dim intStatus As Integer

      ' For getting user information.
      Dim lpName, lpUserName As String

      ' Assign the buffer size constant to lpUserName.
      lpUserName = Space$(lpnLength + 1)

      ' Get the log-on name of the person using product.
      intStatus = WNetGetUser(lpName, lpUserName, lpnLength)

      ' See whether error occurred.
      If intStatus = NoError Then
         ' This line removes the null character. Strings in C are null-
         ' terminated. Strings in Visual Basic are not null-terminated.
         ' The null character must be removed from the C strings to be used
         ' cleanly in Visual Basic.
         GetUserName = Left$(lpUserName, InStr(lpUserName, Chr(0)) - 1)
      Else

         ' An error occurred.
         MsgBox "Unable to get the name."
         End
      End If

Exit_ErrorHandler:
    intStatus = 0
    Set lpName = Nothing
    lpUserName = vbNullString
    Exit Function
    
Err_ErrorHandler:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_ErrorHandler

End Function

This will return the username on the computer when called. In your case it will probably return bsdixon.

So, the trick will be to build a string using this returned value as part of the path where you want to save it to.

This would mean giving up on the use of a macro (not a bad thing whatsoever) and moving to a tiny bit of VBA code to achieve the same thing.

i.e.

Dim strPath As String
strPath = "C:\Documents and Settings\" & GetUserName & "\Desktop"

 

[bsdixon]

I added the module and then I wrote the VBA code to replace the macro. When I click the button, I get the error message Complie Error: Type Mismatch

The & before GetUserName is highlighted.

What am I doing wrong?

 

[bsdixon]

Does anyone know why this is not working?

 

[Groundrush]

If you need to leave the nulls in some of the fields, then you need to account for them with the Nz() function in certain situations.

MySum:Nz([Field1],0) + Nz([Field2],0) + Nz([Field3],0)

Thanks Pat, your solution helped me sort out a problem I had with one of my qry's

A/L Left: Nz([LeaveCarriedOver],0)+Nz([LeaveEntitlement],0)-Nz([HolidaysTaken],0)

works well.....thanks

 

[bsdixon]

Mile-O-Phile:

Do you know what I am doing wrong. I copied the code that you posted directly into a module. I then added the VBA code as an event procedure "on click".

Thanks in advance for your help.

 

[bsdixon]

I am trying to export a query into excel. There are multiple users so I need a place where the file can be stored. I would like for it to save on their desktop or else prompt the user for a file path.

I cannot get the module to work. I copied the VBA code that Mile O Phile posted directly into a module. I did not xhange any of the code. I named the module GetUserName.

I then copied the other code that you post edas an On Click event procedure.

Dim strPath As String
strPath = "C:\Documents and Settings\" & GetUserName & "\Desktop"

I get a Compile Error: Type mismatch when the button is clicked The & before GetUserName is highlighted.

Any help would be greatly appreciated.

 

[Mile-O]

 

[bsdixon]

Mile-O-Phile:

Thanks for the post. The module that you attached is finding the right file path.

The problem that I am having is how do I get this code to look up the module into a macro. I have set up a "transferSpreadsheet" macro. I need the file path that the module looks up to show in the file name box in the macro.

Would I be better off using all VBA code instead of a macro. If so, what code do I use that incorporates the transferSpreadsheet macro and the module to look up the file name.

Sorry for being such a pain. Thanks for the help.

 

[Mile-O]

Mile-O-Phile:

Thanks for the post. The module that you attached is finding the right file path.

The problem that I am having is how do I get this code to look up the module into a macro. I have set up a "transferSpreadsheet" macro. I need the file path that the module looks up to show in the file name box in the macro.

Would I be better off using all VBA code instead of a macro. If so, what code do I use that incorporates the transferSpreadsheet macro and the module to look up the file name.

Sorry for being such a pain. Thanks for the help.

Make a TransferSpreadsheet macro and then convert it to VBA code. The option is in the menu somewhere. Probably under Tools.