Microsoft Access Help & Support

Is there an equivalent for this sql statement in ms access?

SELECT LTRIM(RTRIM(COALESCE(T.Name1, N'') + N' ' + COALESCE(T.Name2, N'')))
FROM dbo.tbl1 T;

5 Comments

Hi, I am trying to compare 2 sheets on one excel workbook.

below code I developed by the help of excel macro. When I click on the run button, I am getting "Invalid procedure call or argument" error.

Please have a look

Public Sub Compare()

Dim ObjExcel
    
    Set ObjExcel = CreateObject("Excel.Application")
    ObjExcel.Visible = False
    ObjExcel.Workbooks.Open "C:\DemoFile.xlsx"

    Set ObjSheet = ObjExcel.ActiveWorkbook.Worksheets(1)
    Set ObjSheet2 = ObjExcel.ActiveWorkbook.Worksheets(2)
    ObjSheet.Activate
    
    ObjSheet.Cells.Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=A1<>ObjSheet2!A1"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

ObjExcel.ActiveWorkbook.Save
    ObjExcel.ActiveWorkbook.Close
    ObjExcel.Quit
    
    Set ObjExcel = Nothing
    Set ObjSheet = Nothing

End Sub

Open in new window

Thank you
A

6 Comments

Hi experts,

How can we accomplish the following in Access.

Situation:
We have a column with an internal codes to identify trees. This internal code is unique for each tree.
But in cases where that tree is divided in 2 or 3 pieces, that internal code gets repeated in that column.

Now for reporting it is needed that we know how many trees there are and how many pieces.

How can we count each unique internal tree code?
I would like to see a column that shows for each record one of the following values: 1 or 0.5 or 0.33.

The values are determined by the counting of the internal tree code.
So if the counting result is
1, show value"1"
2, show value "0.5"
3, show value "0.33"

Am looking forward to a solution.

Microsoft Access

9 Comments

sql cant find input table or query error:

Using the sql below: I keep getting a error message on the syntax ?

strsql_sql = "INSERT INTO [dbo_t_nsc_trackcode_AdminLogIn_and_AdminLogOut_Daily]([NSC_ID_Racfid], [Admin_Logged_Out])" & vbCrLf
strsql_sql = strsql_sql & " VALUES('" & racfid & "', #" & dCurr & "#) where (DateValue([Admin_Logged_In]) = Date and [NSC_ID_Racfid] = '" & racfid & "');"

Thanks
fordraiders

14 Comments

Hi,

I am looking for a vba script which can be run from Access database.
Basically I have 2 tabs on a Excel spreadsheet ie. Source Dataset and Target Dataset.
I am expecting source dataset should be same on Target dataset after migration.
I need to compare both these tabs and if there is any discrepancies/mismatch, apply a color(red) on that cell on both tabs.

Please find the file attached
Really appreciate any help on this. Any questions, please let me know

Thank you
A
DemoFile.xlsx

6 Comments

I am looking for the fates way to get the number of unread emails and the latest email from ms access vba.The inbox has multiple folders

Microsoft Access

3 Comments

Experts,

I am importing data from excel (file name is ImportToAccess.xlsx)
It works fine.
I now only need to import certain records.
The records I would not import would be if there is a match on certain fields.

The fields are:
[Trade #] property is short text
[Buy CP] property is short text
[Quantity BBLS] property is number
[Batch] property is short text

How could I modify the attached code to only import records that do not match between ImportToAccess.xlsx and the file in access that is named the tblImportToAccess.
I am currently deleting all data from tblImportToAccess before I import and I think that part would have to be modified.
I hope that makes sense.
Let me know if you require more information. FYI: I am a novice user of Access and do not have much coding experience.

below please find the code:

Private Sub btnImport_Click_Click()

  
    If MsgBox("Do you want to Import and Delete data?", vbYesNo) <> vbYes Then

       Exit Sub
    End If
       
 CurrentDb.Execute "delete * from [tblImportToAccess]"
    Dim strName As String
    Dim xlApp As Object
    Dim xlWB As Object
    Set xlApp = New Excel.Application
    With xlApp
        .Visible = False
        Set xlWB = .Workbooks.Open("C:\Users\tjohnson\Documents\Bomin\ImportToAccess.xlsx", , False)
       
   strName = "ToImport"
    End With
    Set xlWB = Nothing
    Set xlApp = Nothing

    DoCmd.TransferSpreadsheet acImport, , "tblImportToAccess",

Open in new window

…

Microsoft Access

13 Comments

I want to create a running sum in an Access query that calculates daily cumulative totals. The fields I'm using are Date, Month, Year, and Total. The Total field (column) contains the daily account balance changes (positive or negative) for each day, and these are to be reflected in the running sum total. The table name is tblAcctBalAllFunds_CFYTD.

I've tried the following Dsum statements which produce either blank or incorrect results so I'm guessing I don't have the syntax correct.

DSum("Total","tblAcctBalAllFunds_CFYTD","Date<=" & [Date]) - produces blank results

DSum("Total","tblAcctBalAllFunds_CFYTD","Date<=" & [Adate] & "") - produces blank results (Adate is an alias for Date)

DSum("Total","tblAcctBalAllFunds_CFYTD","DatePart('m', [Date])<=" & [AMonth] & " And DatePart('yyyy', [Date])<=" & [AYear] & "") - produces incorrect results (AMonth and AYear are aliases for Month and Year)

Any suggestions on what to try next would be appreciated.

14 Comments

Checking a table for a date and not time

I have a table called "tblLogInLogOut"

Fields
ID_LOG
LoggedIn -date/time
LoggedOut date/time

When my form opens I need to do a quick check on this table in the LoggedIn field for Todays date .

Sometimes the application freezes(causing a reboot) or computers get turned off and a proper LoggedOut Never occurs.
So if they reboot the pc, AND then open the application --
I need to see if they already logged in for Today
AND IF SO I NEED TO ALERT THEM and do something .

i.e.
The format for the field a is
1/28/2020 11:01:19 AM

4 Comments

I need help parsing out a value from a string in Access Query.

My field has
DS_AS_AT_20150826_10010.pdf

I need to parse out the customer number between "_" and ".pdf"..
The number can be bigger or smaller, but will always precede with the underscore and end with the period.
Is this possible within the access query ?

Microsoft Access

6 Comments

LVL 35

Question by

MAS

2020-01-28Solved

Best way to import 2-3 fields data from one table to another

Best way to import daily 2-3 fields data from one table to another linked table in access

We have a database with attendance. I have linked the required table to access. We need to import the data from a query to a linked table in the same access .

8 Comments

Hi

I am using the following code given to me by an expert to add a column to a linked Access table. How would I alter the code if I wanted to rename an existing column?

Thanks

Sub Add_Column_to_Backend(ByVal oTable As String, ByVal oColumn As String, ByVal oType As String)
    
    Dim strDbName As String 'Database name
    strDbName = GetLinkedDBName(oTable)
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim blnFieldExists As Boolean
    ''Set dbs = CurrentDb()
   
    Set dbs = OpenDatabase(strDbName)
    Set tdf = dbs.TableDefs(oTable)
    
    For Each fld In tdf.Fields
        If fld.Name = oColumn Then
            blnFieldExists = True
            Exit For
        End If
    Next
    
    If Not blnFieldExists Then
        If oType = "Date" Then
           Set fld = tdf.CreateField(oColumn, dbDate)
           fld.DefaultValue = "=Now()"
        ElseIf oType = "Text" Then
            Set fld = tdf.CreateField(oColumn, dbText)
        Else
            Set fld = tdf.CreateField(oColumn, dbText)
        End If
       
        tdf.Fields.Append fld
        MsgBox "New column & '" & oColumn & "' added to table '" & oTable & "'"
    End If
    
End Sub

Public Function GetLinkedDBName(TableName As String)
    Dim db As DAO.Database, Ret
    On Error GoTo DBNameErr
    Set db = CurrentDb()
    Ret = db.TableDefs(TableName).Connect
    GetLinkedDBName = Right(Ret, Len(Ret) - (InStr(1, Ret, "DATABASE=") + 8))
    Exit Function

Open in new window

…

10 Comments

LVL 35

Question by

MAS

2020-01-27Solved

Punchin and punchout time access database

Get min and Max value.
I have a database for attendance. Below is the only two fields in the database.
EmpID PUNCHDATETIME.
1 27/1/2020 10:00:00
1 27/1/2020 13:00:00
1. 27/1/2020 17:00:00
2 27/1/2020 10:12:00
2 27/1/2020 13:10:00
2. 27/1/2020 17:15:00
3. 27/1/2020 10:08:00
3. 27/1/2020 13:00:00
3. 27/1/2020 17:06:00

I want to see take out min time as punch in and import to HR system
And take Max time as punch out and import to HR system.
How to create a query or update to get the data.

Thanks

9 Comments

In a query designer I am trying to figure out how to extract the characters up to the first ( mark and put them in a new field in the query. So for example:

XYZ012 would return 012
and
ABC12 would return 12

Microsoft Access

4 Comments

Is there a way to save all changes to he modules via a command button etc. in ms access.

Microsoft Access

7 Comments

LVL 4

Question by

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc

2020-01-27Solved

How to insert Data into SQL Server

The code below works very well against Ms Access VBA but the same code does not insert anything against Ms SQL Server, any assistance will be highly appreciated. It seams like SQL does not like the command UPDATE I still think it wants a reference or Primary ID number while access accept without problems. The primary ID number cannot be there until the record is created, so in place of UPDATE is there something that need to be done to insert the processed data into the SQL table????

Dim JSONS As Object

    lngStatus = CommRead(intPortID, strData, 14400)

Set rs = db.OpenRecordset("tblEfdReceipts")
    If lngStatus > 0 Then
    Set JSONS = JsonConverter.ParseJson(strData)
    Z = 2
    ElseIf lngStatus < 0 Then
    Beep
    MsgBox "Please note that there is no data to read", vbOKOnly, "The Comm Port has no data"
        ' Handle error.
        On Error Resume Next
    End If
        ' Process data.
  
  For Each Item In JSONS
           With rs
         
            .AddNew
            rs![TPIN] = Item("TPIN")
            rs![TaxpayerName] = Item("TaxpayerName")
            rs![Address] = Item("Address")
            rs![ESDTime] = Item("ESDTime")
            rs![TerminalID] = Item("TerminalID")
            rs![InvoiceCode] = Item("InvoiceCode")
            rs![InvoiceNumber] = Item("InvoiceNumber")
            rs![FiscalCode] = Item("FiscalCode")
            rs![TalkTime] = Item("TalkTime")
            rs![Operator] = Item("Operator")
            rs![Taxlabel] =

Open in new window

…

6 Comments

Hi

Is there anybody here with an idea of a vendor who is selling some Ms Access VBA books dealing with virtual serial ports using window API with the following features:

Header 1       1       The first byte of package header 0x1A
Header 2       1       The second byte of package header Ox5D
CmdID       1       Command IDs:
0x01 acquire the status of ESD
0x02 invoice signing
0x03 Error code
Length       4       The length of the content, big-endian
Content       ？       The Json based business data
CRC       2       Two-Byte verification (CRC), it will be generated by bytes start from Header 1 up to content

I have seen the code written by Mr David M H. that can only sort out simple connectivity that does not require , the ( Header1,Header2, CmdID & CRC)
Though the last solution to this if the books cannot be found is to buy an already available code.

For Example:

(1) CRC is used to check the parameter from from Header 1 to content ( 2 byte length)
(2) CmdID is used to ( Open the port, write data & read data) ( 1 byte length)
(3) Header 1 is the starting point or the address package ( 1 byte length)
(4) Header 2 is the of end of address ( 1 byte length)
(5) Length is the length of the content ( 4 byte length)
(6) Content is the Json business data ( ?byte length)
(7) CRC this one is used to check for errors or catch errors ( 2 byte length)

Final code

Below is the final code to be sent to the serial port:

<H eader 1<> H eader 2<> CmdID<> Length <>Content <>CRC>

Open in new window

Regards

Chris

4 Comments

I have a main form and subform. The subform is a single record form with 20 records available. When I click a button on the subform data for all 20 records are updated (I'm just changing a date field). How do I get Access to save all records so that when I move to another single record and click the same button as on the previous form I don't get error "another user has made changes do you want to save drop change etc, etc."

Microsoft Access

9 Comments

Hello,

I need to update a table: tblImportToAccess.[TradeNo] and tblImportToAccess.[CoName]
with the value that is in the row above it.

In the pic above, I need the nulls on [TradeNo] and [CoName] to populate with 123 and XYZ respectively for the first null but the second null I would need it to populate with 333 and ABC respectively (much like dragging down the cell above if it were in excel).

My dataset is much larger and there are different TradeNo and CoName.
I am not sure if this would best be done by way of an update query but if so then I have a part of it below and looking for the remainder.
I am a novice user of Access. I can easily do this in excel with a macro but I want to to perform this in access after I import the excel data.

UPDATE tblImportToAccess SET
WHERE (((tblImportToAccess.[TradeNo]) Is Null) AND ((tblImportToAccess.[CoName]) Is Null));

Thank you.

14 Comments

Question by

pdvsa

2020-01-26Solved

Report Grouping

Hello,

I have a report grouped first by [Date] then by [CoName].
The report groups on date as I want but I need to include all other [CoName] with older dates BUT ONLY within that month.
Currently, those [CoName] records are bumped further down in the same month because of the sort I have.

example:
Co A with all dates in January need to be grouped together in January and sort needs to be by Date oldest to newest.

I hope that makes sense. If not please let me know.

I have attached a pared down db and there is only 1 rpt and 1 tbl
thank you
EE.accdb

14 Comments

Question by

Tony

2020-01-26Solved

Mysql Vpn

Mysql VPN.
HI..how do I share or connect Mysql using a Internet private VPN? NOTE: I ms access as a front end....also Work bench to manage

6 Comments

Hi..as you know ms-access saves the information automatically by default. How do I SAVE the information with a button instead of save it automatically? I mean...the user will save after changes. Take into account that I am using a Head form and a detail form. For instance....an Invoice...it has FormInvoiceHead... and FormInvoiceDetail....both forms linked.....Father-child.... how do I SAVE the information using both forms....taking into account that is related info?? Note: I use mysql as a back end.

20 Comments

Hi

I am using Access VBA code to load a text box in rich text. The problem is that when my form opens all the text is selected. Hoe do I unselect the text? Thanks

Private Sub Form_Load()

''Me.Text0 = "<div>ArmsTracker Help Menu This is an example of Armstracker's help menu </div>"

Me.Caption = "Register an Arm"

Dim S As String
S = "<div>"

S = S & "Help - Register an Arm"
S = S & " "
S = S & " "
S = S & "Search for Existing Licensee"
S = S & " "
S = S & "Select ?Search?, then start typing the licence number or the name of the licensee. Select the correct entry."
S = S & " "
S = S & " "
S = S & "Or: Add New Licensee"
S = S & " "
S = S & "Before you can register an arm to an owner, that person or entity must have a licence. By selecting 'Add New Licensee' you begin the process"
S = S & " "
S = S & " "
S = S & "Date Registered"
S = S & " "
S = S & "Choose from the pop-up calendar or type the full date (dd/mm/yyyy)"
S = S & " "
S = S & " "
S = S & "Manufacturer"
S = S & " "
S = S & "Select ?Search?, then start typing the name of the manufacturer. In some cases entering a model name might also find the manufacturer. Select the correct entry."
S = S & " "
…

5 Comments

Hi

The following Access VBA code has an If then Statement that for 64 bit vs 32 bit code. Why does the first part (64 bit) code compile when I am in Access 32 bit and the second part (32 bit) does NOT compile when I am in a 64 bit version
I am pretty sure that this is not possible but can you somehow have the 32 bit code there and tell the compiler to avoid it

#If VBA7 Then
    Declare PtrSafe Function GetSystemMetrics32 Lib "User32" _
        Alias "GetSystemMetrics" (ByVal nIndex As Long) As Long
    Declare PtrSafe Function SetWindowPos Lib "user32.dll" (ByVal hwnd As LongPtr, ByVal hWndInsertAfter As LongPtr, _
        ByVal X As Long, ByVal Y As Long, ByVal cx As Long, ByVal cy As Long, ByVal wFlags As Long) As Long
        
    Private Declare PtrSafe Function GetTempPath Lib "kernel32" Alias "GetTempPathA" _
     (ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long
    Private Declare PtrSafe Function SHGetSpecialFolderLocation _
        Lib "shell32" (ByVal hwnd As Long, _
        ByVal nFolder As Long, ppidl As LongPtr) As Long
    Private Declare PtrSafe Function SHGetPathFromIDList _
        Lib "shell32" Alias "SHGetPathFromIDListA" _
        (ByVal Pidl As LongPtr, ByVal pszPath As String) As Long
    Private Declare PtrSafe Sub CoTaskMemFree Lib "ole32" (ByVal pvoid As LongPtr)
    Private Declare PtrSafe Function SHBrowseForFolder Lib "shell32.dll" _
         Alias "SHBrowseForFolderA" (lpbi As BROWSEINFO) As Long
    Declare PtrSafe

Open in new window

…

7 Comments

Hi Experts,

I have just completed a report where I apply code like the following in the Report Load event to various fields. I have the following Sections in the report:
- Page Header.
- Section 3 Header - This is where the fileds are that are being formatted.
- Detail.
- Page Footer.

'Step 811-Apply Group Pre-Registration Fee Amount Background Colour.
WrkFieldAmt001 = Me.WrkGroupPreRegFeeAmt
Dim WrkDisplayGroupPreRegFeeAmt811 As Currency
WrkDisplayGroupPreRegFeeAmt811 = 0
WrkDisplayGroupPreRegFeeAmt811 = WrkFieldAmt001
'Me.WrkGroupPreRegFeeAmt.BackStyle = 0 'Trasparent
Me.WrkGroupPreRegFeeAmt.BackColor = ColourWhite001
Select Case WrkDisplayGroupPreRegFeeAmt811
Case Is = 0
Me.WrkGroupPreRegFeeAmt.BackStyle = 0 'Trasparent
Me.WrkGroupPreRegFeeAmt.BackColor = ColourWhite001
GoTo STEP_812 'Step 812-Apply Group Pre-Registration Total Payments Amt Background Colour.
Case Is < 0
Me.WrkGroupPreRegFeeAmt.BackStyle = 1 'Normal
Me.WrkGroupPreRegFeeAmt.BackColor = ColourLiteRed001
GoTo STEP_812 'Step 812-Apply Group Pre-Registration Total Payments Amt Background Colour.
Case Is > 0
Me.WrkGroupPreRegFeeAmt.BackStyle = 1 'Normal
Me.WrkGroupPreRegFeeAmt.BackColor = ColourLiteGreen001
GoTo STEP_812 'Step 812-Apply Group Pre-Registration Total Payments Amt Background Colour.
Case Else
Me.WrkGroupPreRegFeeAmt.BackStyle = 0 'Trasparent
…

Microsoft Access

8 Comments

Microsoft Access

Related Topics

Microsoft Access

Related Topics