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.

 

[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??

 

[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!

 

[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.