Date Picker 5

[diggerbob]

I have used a date picker which uses a calendar on my form for sometime now and have no problem with it. I copied this to a new database that I am starting for someone else at work but every time I try to run it, I get the following error message: Run-Time Error '3075', Syntax error (missing operator) in query expression (Received Date Between #02/01/2008 And #02/18/2008#). I have even made another form from scratch and I still get the same error. The only thing I am doing differently is retreiving their table data from an import from excel.

 

[diggerbob]

Yes, I have got it working, and yes, I could kick myself... for not seeing that! I appreciate everyone's help on this.
Bob K

 

[MajP]

Do yourself a big favor and get rid of spaces in names for any object in Access and vba. You are begging for problems in Sql and code. Also avoid any special characters and reserved words. There are several "standard" naming conventions out there (google "Sql Naming Conventions" or "Access naming conventions"), pick one. Example

SELECT VendorName,PO_No, AccountCode, DateReceived, AES_ItemNo, PurchaseOrderDescription, CostCenter, ReceiveToID, QtyReceivedUOP, OriginalUnitCost, qtyReceivedUOP * OriginalUnitCost AS ReceivedTotal, ItemTotal, ItemTotal- ReceivedTotal AS AmountNotReceived
FROM tblAccrual;

versus yours

SELECT [Accrual Table].[Vendor Name], [Accrual Table].[PO No], [Accrual Table].[Account Code], [Accrual Table].[Date Received], [Accrual Table].[AES Item No], [Accrual Table].[Purchase Order Description], [Accrual Table].[Cost Center], [Accrual Table].[Receive To ID], [Accrual Table].[Qty Received (UOP)], [Accrual Table].[Original Unit Cost], [Accrual Table]![Qty Received (UOP)]*[Accrual Table]![Original Unit Cost] AS [Received Total], [Accrual Table].[Item Total], [Item Total]-[Received Total] AS [Amount Not Received]
FROM [Accrual Table];

 

[ZmrAbdulla]

And see the beauty & readability of sql if you have formatted like

SELECT
    VendorName
  , PO_No
  , AccountCode
  , DateReceived
  , AES_ItemNo
  , PurchaseOrderDescription
  , CostCenter
  , ReceiveToID
  , QtyReceivedUOP
  , OriginalUnitCost
  , qtyReceivedUOP * OriginalUnitCost AS ReceivedTotal
  , ItemTotal
  , ItemTotal- ReceivedTotal AS AmountNotReceived
FROM
   tblAccrual;

SELECT
    [Accrual Table].[Vendor Name]
  , [Accrual Table].[PO No]
  , [Accrual Table].[Account Code]
  , [Accrual Table].[Date Received]
  , [Accrual Table].[AES Item No]
  , [Accrual Table].[Purchase Order Description]
  , [Accrual Table].[Cost Center]
  , [Accrual Table].[Receive To ID]
  , [Accrual Table].[Qty Received (UOP)]
  , [Accrual Table].[Original Unit Cost]
  , [Accrual Table]![Qty Received (UOP)]*[Accrual Table]![Original Unit Cost] AS [Received Total]
  , [Accrual Table].[Item Total]
  , [Item Total]-[Received Total] AS [Amount Not Received]
FROM
   [Accrual Table];

________________________________________________________
Zameer Abdulla
Help to find Missing people

 

[diggerbob]

MajP,
I understand that I should do that but that data will be imported into the table from an excel sheet. The person using this has to export the data from a database into an Excel worksheet. Then in turn, import it into Access. This will be done on a monthly basis. The names in the access table is exactly the way they come over from excel. Is there any way around that?
Thanks, BobK

 

[ProgramError]

Hi Bob,

I'm currently working on different methods of importing Excel data into tables. I'll let you know how I'm getting on. If your excel sheet doesn't differ each time in its format, then you can import the information by any sequence of the cells you wish. Programatically in VBA, using for next loops or by using a table to read the cell map indexes.



Ian Mayor (UK)
Program Error
Always make your words sweet and nice. Because you never know when you may have to eat them.

 

[diggerbob]

Ian,

Thanks, and will be looking forward to hearing from you.

BobK