Solved

Runtime error 3075

Posted on 2011-09-27
16
297 Views
Last Modified: 2012-05-12

 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
0
Comment
Question by:PipMic
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
16 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36710564
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
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 36710767
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
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 36711363
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:PipMic
ID: 36712624
Hi capricorn 1 ,

None of the options worked!!

0
 

Author Comment

by:PipMic
ID: 36712627
Hi fyed,

Didnt work either!!
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36712754
PipMic,
upload a copy of db
0
 

Author Comment

by:PipMic
ID: 36712817
Hi capricorn 1

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

Compile error
Expected array

:)
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36712850
that does not help... upload the db
0
 

Author Comment

by:PipMic
ID: 36712890
here goes
testv2003.mdb
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 200 total points
ID: 36713029


use this


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

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 200 total points
ID: 36713046
test this
testv2003Rev.mdb
0
 

Author Comment

by:PipMic
ID: 36713110
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
 

Author Comment

by:PipMic
ID: 36813630
Hi capricorn,

It worked ..thanks.   :)
0
 

Author Comment

by:PipMic
ID: 36813639
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
 

Author Comment

by:PipMic
ID: 36813641
Gosh Ive made a mistake... I want to award Capricorn1 with all the points...


Please help
0
 

Author Closing Comment

by:PipMic
ID: 36813651
brilliant
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question