Solved

Runtime error 3075

Posted on 2011-09-27
16
293 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
16 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
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 47

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
 

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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
ID: 36712850
that does not help... upload the db
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

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

Assisted Solution

by:Rey Obrero
Rey Obrero 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 119

Accepted Solution

by:
Rey Obrero 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

914 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now