Solved

Runtime error 3075

Posted on 2011-09-27
16
296 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 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 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Suggested Solutions

Title # Comments Views Activity
How can I clear all records from access continuous form 4 51
Access Combo Box Filters for Active / Inactive based on Check Box 8 45
Access syntax 1 33
Null or "" 28 52
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

685 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