如何通过VBA宏校验数据是否匹配用户输入的年月参数？

Let's expand your existing macro to handle both checking for matching data and picking the right query based on that check. Here's a step-by-step breakdown:

Step 1: Validate User Input First #

First, we should make sure the user enters valid numbers for year and month—no blank entries or random text. Let's add basic checks for this:

Sub macro1()
    Dim year As Integer
    Dim month As Integer
    Dim inputYear As String
    Dim inputMonth As String
    
    ' Get and validate year input
    inputYear = InputBox("What year would you want to get data from?")
    If Not IsNumeric(inputYear) Or inputYear = "" Then
        MsgBox "Please enter a valid year (e.g., 2024)", vbExclamation
        Exit Sub
    End If
    year = CInt(inputYear)
    
    ' Get and validate month input
    inputMonth = InputBox("What month would you want to get data from?")
    If Not IsNumeric(inputMonth) Or inputMonth = "" Or CInt(inputMonth) < 1 Or CInt(inputMonth) > 12 Then
        MsgBox "Please enter a valid month (1-12)", vbExclamation
        Exit Sub
    End If
    month = CInt(inputMonth)

Step 2: Check if Matching Data Exists #

Next, we'll verify if there's data in your table/query that matches the entered year and month. Here are two simple, reliable methods:

Method 1: Use DCount (Simplest for Existence Checks) #

DCount counts records that meet your criteria. If the count is greater than 0, data exists:

' Replace placeholders with your actual table/field names
    Dim recordCount As Long
    recordCount = DCount("*", "YourDataTable", "[YearColumn] = " & year & " AND [MonthColumn] = " & month)
    
    Dim dataExists As Boolean
    dataExists = (recordCount > 0)

Method 2: Use a Recordset (Better for Future Data Processing) #

If you might need to work with the matching data later, use a recordset instead:

Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM YourDataTable WHERE [YearColumn] = " & year & " AND [MonthColumn] = " & month)
    
    Dim dataExists As Boolean
    dataExists = Not rs.EOF ' EOF means "end of file"—if we're not there, records exist
    rs.Close
    Set rs = Nothing

Step 3: Run the Appropriate Query #

Now, based on whether data exists, execute the query you need. Replace the query names with your actual ones:

' Trigger the right query based on the check
    If dataExists Then
        DoCmd.OpenQuery "QueryForExistingData" ' Use this if data is found
        MsgBox "Found data for " & month & "/" & year & ". Running your existing data query.", vbInformation
    Else
        DoCmd.OpenQuery "QueryForNoData" ' Use this if no data exists
        MsgBox "No data found for " & month & "/" & year & ". Running your fallback query.", vbInformation
    End If
End Sub

Quick Notes to Customize #

• Swap Placeholders: Make sure to replace YourDataTable, [YearColumn], [MonthColumn], QueryForExistingData, and QueryForNoData with your actual table/field/query names.
• Single Date Field?: If your data uses a single date column instead of separate year/month fields, adjust the criteria like this:

  recordCount = DCount("*", "YourDataTable", "Year([DateColumn]) = " & year & " AND Month([DateColumn]) = " & month)
  

• Extra Error Handling: You can add more robust error trapping (like On Error GoTo) if you need to handle edge cases like missing tables/queries.

内容的提问来源于stack exchange，提问作者yoni