Runtime error 3075


 Hi,

I have the following code which is an adaptation of some other code I obtained. With it I am trying to check three different fields.

The error I'm getting the following error message: Syntax Error (missing operator in query expression.....



Private Sub uploading_data_to_ptf_Click()
Dim AppendDate As Date
Dim Source As String

    AppendDate = Forms![Uploading Menu]![contridate]
    Source = Forms![Uploading Menu]![wherefrom]
   
    If DCount("[Period] & [System]", "tbl_transactions", "Month([Period])= " & Month(AppendDate) & " AND " & "Year([Period]) =" & Year(AppendDate) & " AND " & "([System]) =" & Source()) > 0 Then
    MsgBox "Error", vbCritical
    Else
        DoCmd.RunMacro "Uploading Data to program"
    End If
   
End Sub

Grateful if someone could find the problem with it!  thanks
PipMicAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
test this

    If DCount("[Period] & [System]", "tbl_transactions", "Month([Period])= " & Month(AppendDate) & " AND Year([Period]) =" & Year(AppendDate) & " AND [System] =" & Source()) > 0 Then


what is the Data Type of the field  [System] , Text or Number ?

if Text use this

   If DCount("[Period] & [System]", "tbl_transactions", "Month([Period])= " & Month(AppendDate) & " AND Year([Period]) =" & Year(AppendDate) & " AND [System] ='" & Source() & "'") > 0 Then

0
Helen FeddemaCommented:
Certain data types need delimiters in expressions -- Date values need # (or Chr(35)), and String values quotes, or Chr(39), which I prefer because it is more readable.  Here are some examples:
'Numeric filter
   lngID = Nz(Me![ID])
   If lngID <> 0 Then
      strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
         & "[ID] = " & lngID & ";"
   End If

   'String filter
   strInventoryCode = Nz(Me![InventoryCode])
   If strInventoryCode <> "" Then
      strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
         & "[InventoryCode] = " & Chr$(39) & strInventoryCode & Chr$(39) & ";"
   End If

   'Date range filter from custom database properties
   dteFromDate = CDate(GetProperty("FromDate", ""))
   dteToDate = CDate(GetProperty("ToDate", ""))
   strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
      & "[dteDateReceived] Between " & Chr(35) & dteFromDate _
      & Chr(35) & " And " & Chr(35) & dteToDate & Chr(35) & ";"

   'Date range filter from controls
   If IsDate(Me![txtFromDate].Value) = True Then
      dteFromDate = CDate(Me![txtFromDate].Value)
   End If

   If IsDate(Me![txtToDate].Value) = True Then
      dteToDate = CDate(Me![txtToDate].Value)
   End If

   strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
      & "[dteDateReceived] Between " & Chr(35) & dteFromDate _
      & Chr(35) & " And " & Chr(35) & dteToDate & Chr(35) & ";"

Open in new window

0
Dale FyeOwner, Developing Solutions LLCCommented:
Try:

    Dim strCriteria as string
    strCriteria = "Format([Period], ""yyyymm"") = " & chr$(34) & Format(AppendDate, "yyyymm") & chr$(34) _
                     & " AND [System] =" & Source()
    If DCount("[Period] & [System]", "tbl_transactions", strCriteria) > 0 Then
        MsgBox "Error", vbCritical
    Else
        DoCmd.RunMacro "Uploading Data to program"
    End If
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

PipMicAuthor Commented:
Hi capricorn 1 ,

None of the options worked!!

0
PipMicAuthor Commented:
Hi fyed,

Didnt work either!!
0
Rey Obrero (Capricorn1)Commented:
PipMic,
upload a copy of db
0
PipMicAuthor Commented:
Hi capricorn 1

Apologies the it didnt meant that I got the following message!

Compile error
Expected array

:)
0
Rey Obrero (Capricorn1)Commented:
that does not help... upload the db
0
PipMicAuthor Commented:
here goes
testv2003.mdb
0
Rey Obrero (Capricorn1)Commented:


use this


    If DCount("[Period] & [System]", "tbl_transactions", "Month([Period])= " & Month(AppendDate) & " AND Year([Period]) =" & Year(AppendDate) & " AND [System] ='" & Source & "'") > 0 Then
0
Rey Obrero (Capricorn1)Commented:
test this
testv2003Rev.mdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PipMicAuthor Commented:
Hi,

It seems to work....will have to try tomorrow at work on the real one...many thanks

I will get back to you.asap.

But before I go can you at least explain the code?
0
PipMicAuthor Commented:
Hi capricorn,

It worked ..thanks.   :)
0
PipMicAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for PipMic's comment http:/Q_27343671.html#36712890

for the following reason:

brilliant
0
PipMicAuthor Commented:
Gosh Ive made a mistake... I want to award Capricorn1 with all the points...


Please help
0
PipMicAuthor Commented:
brilliant
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.