#error in text box

I have this code in a  text box

= dsum("[Amount]", "[July 09 - On  Billings and Adjustments Data Query]", 
"[Customer No] = " & [cboCustomerNumber] & " AND Format([Posting Date 
Period], ""yyyymm"") = '" & Format([cboPostingDate], "yyyymm") & "'")

i keep getting #error in my text box

i'm struggling to find they answer to why


the query July 09 - On  Billings and Adjustments Data Query
has fields of 

customer Number
posting periood date
Amount

Now i have two combo boxes on my form which the text box should look up

Customer number and Name, are bound to another table 

No (for customer Number)
Name ( for customer Name

Customer Listing Table

not linked  to July 09 - On  Billings and Adjustments Data Query

Is this what causing my error and how do i fix it

Thanks in advanced

0
Utf
3/25/2010 10:58:01 AM
access.modulesdaovba 1670 articles. 0 followers. Follow

3 Replies
849 Views

Similar Articles

[PageSpeed] 16

On 25 mrt, 11:58, Alan <A...@discussions.microsoft.com> wrote:
> I have this code in a =A0text box
>
> =3D dsum("[Amount]", "[July 09 - On =A0Billings and Adjustments Data Quer=
y]",
> "[Customer No] =3D " & [cboCustomerNumber] & " AND Format([Posting Date
> Period], ""yyyymm"") =3D '" & Format([cboPostingDate], "yyyymm") & "'")
>
> i keep getting #error in my text box
>
> i'm struggling to find they answer to why
>
> the query July 09 - On =A0Billings and Adjustments Data Query
> has fields of
>
> customer Number
> posting periood date
> Amount
>
> Now i have two combo boxes on my form which the text box should look up
>
> Customer number and Name, are bound to another table
>
> No (for customer Number)
> Name ( for customer Name
>
> Customer Listing Table
>
> not linked =A0to July 09 - On =A0Billings and Adjustments Data Query
>
> Is this what causing my error and how do i fix it
>
> Thanks in advanced

Dates should be delimited by #. Try:
=3D dsum("[Amount]", "[July 09 - On  Billings and Adjustments Data
Query]",
"[Customer No] =3D " & [cboCustomerNumber] & " AND Format([Posting Date
Period], ""yyyymm"") =3D #" & Format([cboPostingDate], "yyyymm") & "#")


Groeten,

Peter
http://access.xps350.com
0
XPS350
3/25/2010 1:03:41 PM
still not working,

Any other ideas?

"Alan" wrote:

> I have this code in a  text box
> 
> = dsum("[Amount]", "[July 09 - On  Billings and Adjustments Data Query]", 
> "[Customer No] = " & [cboCustomerNumber] & " AND Format([Posting Date 
> Period], ""yyyymm"") = '" & Format([cboPostingDate], "yyyymm") & "'")
> 
> i keep getting #error in my text box
> 
> i'm struggling to find they answer to why
> 
> 
> the query July 09 - On  Billings and Adjustments Data Query
> has fields of 
> 
> customer Number
> posting periood date
> Amount
> 
> Now i have two combo boxes on my form which the text box should look up
> 
> Customer number and Name, are bound to another table 
> 
> No (for customer Number)
> Name ( for customer Name
> 
> Customer Listing Table
> 
> not linked  to July 09 - On  Billings and Adjustments Data Query
> 
> Is this what causing my error and how do i fix it
> 
> Thanks in advanced
> 
0
Utf
3/25/2010 2:39:01 PM
On Thu, 25 Mar 2010 03:58:01 -0700, Alan wrote:

> I have this code in a  text box
> 
> = dsum("[Amount]", "[July 09 - On  Billings and Adjustments Data Query]", 
> "[Customer No] = " & [cboCustomerNumber] & " AND Format([Posting Date 
> Period], ""yyyymm"") ) = '" & Format([cboPostingDate], "yyyymm") & "'")
> 

> i keep getting #error in my text box
> 
> i'm struggling to find they answer to why
> 
> the query July 09 - On  Billings and Adjustments Data Query
> has fields of 
> 
> customer Number
> posting periood date
> Amount
> 
> Now i have two combo boxes on my form which the text box should look up
> 
> Customer number and Name, are bound to another table 
> 
> No (for customer Number)
> Name ( for customer Name
> 
> Customer Listing Table
> 
> not linked  to July 09 - On  Billings and Adjustments Data Query
> 
> Is this what causing my error and how do i fix it
> 
> Thanks in advanced

Try the Expression this way:
= DSum("[Amount]", "[July 09 - On  Billings and Adjustments Data
Query]", "[Customer No] = " & [cboCustomerNumber] & " AND
Format([Posting Date Period],'yyyymm') = """ &
Format([cboPostingDate], "yyyymm") & """")

The above assumes that cboCustomerNumber and cboPostingDate contain
valid Customer Numbers and valid dates.

I would suspect, from the name of the Query (July 09 etc.), that you
might not have a normalized data structure. 
Do you have a different query for each week or day of the week?
-- 
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
0
fredg
3/25/2010 5:19:05 PM
Reply:

Similar Artilces:

Dependent Formatting (Text or Numbers
Hello All, I am attempting create formatting for an object based upon the value displayed. If the value is six numbers entered without spaces (Ex. 123456), then the formatting should be "##-##-##" (Ex. 12-34-56). Otherwise, the object must allow regular text. I have tried several different methods but with no luck. Below is my most recent attempt. I tried inserting it into the "lost focus", "on change", and "after update actions". Many thanks in advance for helping clear up a headache. If Me.AccountCode1 Like "######" Then Me.AccountCode...

Avoid automatic text to column split while pasting data
I am copying data from Access and pasting it in Excel. The column containing "LastName, FirstName" is automatically getting pasted in 2 columns. I want it to get pasted in one column only. Currently ColA ColB John| Doe I need it to be ColA Thank you! John, Doe You probably used Data>Text to Columns>Delimited by "comma" for some other operation. Excel remembers this.........thank you much<g> Run through Data>Text to Columns with some dummy text and change the delimiter to nothing. Gord Dibben MS Excel MVP On Wed, 25 Jun 2008 09:41:00 -0700, Query...

Exporting EXCEL data in text (cvs) format
Is there no way to export EXCEL data to a text format e.g. cvs : "StringField1","StringField2",NumericField3,NumericField4,"StringField5" etc.? You can import from this format, why no export? Any work around? I'm the first person in the universe who wants an importable text version of his EXCEL data? Thanks! Woops! I didn't see the other options in the save as pulldown list. Sorry! Don't bother! "Simon Wigzell" <simonwigzell@shaw.ca> wrote in message news:2FCNb.115599$X%5.16110@pd7tw2no... > Is there no way to export EXCEL da...

attachment size errors
I keep getting an error the attachment size to too large...any idea where I can increase the criteria so I can send emails with larger attachments? ...

Free/Busy Errors
We recently migrated Exchange servers from an Exchange 2000 server to Exchange 2003. Everything was going great until I brought the old Exchange 2000 server offline. It appears there are a few additional hidden locations that have the origional server as master. I have changed the recepient update service and other obvious areas. But my clients are getting an error, "Unable to update public free/busy data. The contents of the this public folder are currently unabailable." I get three events in the event viewer: MSExchangeSA 9335 OALGen encountered error 8004010f while ...

Word Forms
Can anyone give me the text for a macro to do the above, or advise me how to write this macro in word A tock? or a tick? ;) See http://www.gmayor.com/Macrobutton.htm -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> "Aidan" <Aidan@discussions.microsoft.com> wrote i...

Installation Failure: Error Code: 0x64C
How Can I resolve the following issue? Installation Failure: Error Code: 0x64C Microsoft .NET Framework 1.1 Service Pack 1 Security Update for Windows 2000, Windows XP, Windows Vista, Windows Server 2008, Windows 7, and Windows Server 2008 R2 (KB953297) Your help is appreciated! Luis quadspark wrote: > How Can I resolve the following issue? > > Installation Failure: Error Code: 0x64C > Microsoft .NET Framework 1.1 Service Pack 1 Security Update for Windows > 2000, Windows XP, Windows Vista, Windows Server 2008, Windows 7, and > Windows > Server...

Passing a cell value into a text box on a graph
Please help. I am trying to pass a value from a cell onto a graph. The graph is plotting culumative variance against a plan but the gap in the early days is very small. So I need to publish the gap between plan and actual on the graph. I want to automate the update of the gap on opening the spreadsheet or pressing a button. The calculation of the gap is no problem for me, its the putting the answer onto the graph is the issue. I thought I could do it via a text box on the graph. Thanks in advance John Please help. I am trying to pass a value from a cell onto a graph. The graph is plot...

error regarding enabling macros
When I go into Outlook 2000, I get a message that says "Some files added to Outlook contrain macros. Be sure to check Enable Macros when prompted to ensure that the customer manager functions correctly". When I click "ok" and "enable macros", I get a message that says "Unknown Error 0X9494010F The requested object does not exist". I have looked everywhere. It seems to have something to do with the Small Business Customer Manager, but I don't know what. Any advice is greatly appreciated. ...

Error moving/copying messages from 1 folder to another
The following seems to apply to many mailboxes in our Exchange 5.5 environment. I am tryinig to move messages from one folder to another and I constantly get the following message: "Can't copy the items. Some items could not be copied. They were either moved or deleted, or access was denied." I have tried to move these using the exchange admin account. I have tried different versions of Outlook (2000 / 2002). I have tried to copy/move to different folders. I have noticed that I can do one message at a time, but only if I open the message first. Obviously I can do this for th...

formatted colored text
When I format text and/or numbers to be in color, it continues to show as black and white on the screen. What am I doing wrong? Check this out................ http://support.microsoft.com/default.aspx?kbid=833258 The fill color, the fill pattern, or the line color of a WordArt or AutoShape object in an Office document does not change As directed, change the High Contrast setting in Windows OS for users' profiles. Gord Dibben MS Excel MVP On Fri, 28 May 2010 14:08:09 -0700, Brenda <Brenda@discussions.microsoft.com> wrote: >When I format text and/or ...

OL2000: BUG: Outlook composes message in Rich text format when the "mailto" link contains the "body" parameter
Example: mailto:xxx@xxx.xxx?subject=hi - opens a new message in plain text or HTML text, based on the default setting mailto:xxx@xxx.xxx?subject=hi&body=test - opens a new message in Rich text. User then has to manually change to plain text. How can I submit this bug to Microsoft? Thanks, Ivan Ivan B�tora <xxx@xxx.xxx> wrote: > Example: > > mailto:xxx@xxx.xxx?subject=hi - opens a new message in plain text or > HTML text, based on the default setting > > mailto:xxx@xxx.xxx?subject=hi&body=test - opens a new message in Rich > text. User then has to manu...

working out which colour text is used to populate a cell
Hi all, Fingers crossed someone out there will be able to help me. I have been tasked to provide a spreadsheet at work to monitor a number of applications and written applications that we receive and the number of days we respond by. I have the following cells ‘application received’(Cell B), ‘enquiry received’(Cell C), ‘date completed’(Cell E) I then have the following formula in Cell H =NETWORKDAYS(IF(B4<>"",B4,C4),E4,$A$119:$A$158) which determines which cell has the date in (B or C) and then works out the number of days it has taken looking at cell E (the...

Wordwrap failure in long text
Excel 2000 SP-3, Win 98. I often have long text cells, with several paragraphs. At some point, they all get the same sickness, wordwrap doesn't work anymore. The lines that wrapped up to that point still do. After the mystery point, the only way to get a line break is Alt-Enter. Mid-December 03, there was a short thread on this in Crashesgpfs (look up "Maximum row size"). The one answer was that there is a max row size of 409, so after 409 chars no more wordwrap. Sounds like my problem. The workaround would be to quit the current cell at the previous paragraph break,...

Convert text to number
Hi people, Thanks a lot to everybody. Unfortunatelly I cannot attach .xls file that I receive, because of excelforum's restrictions, it looks like usual excel file. I will try to use the advice of Debra. Thanks again, Irina. -- Irina ------------------------------------------------------------------------ Irina's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=8685 View this thread: http://www.excelforum.com/showthread.php?threadid=263454 ...

Circular reference error closes workbook
When I one a certain workbook I get a dialog box saying I have a circular reference. If I click OK or Cancel the workbook saves and exits. I can find and deal with circular references but I can't keep the workbook open. Can anyone help? Steve H Just a wild guess.... Try opening excel in safe mode: Close Excel windows start button|run excel /safe File|open your workbook If it stayed open, fix the circular reference and save. Then close excel and try opening it normally. SteveH wrote: > > When I one a certain workbook I get a dialog box saying I have a circular > refere...

Workflow Error
Hi there, Please help me with this workflow issue that we are having. Whenever I click on "Workflow" next to a document in a document library, it right away says "Fail on Start". So i went to the logs on the server and found the following error msg: 01/06/2010 08:19:04.75 w3wp.exe (0x1B80) 0x1824 Windows SharePoint Services Workflow Infrastructure 98d7 Unexpected System.ArgumentException: Value does not fall within the expected range. at Microsoft.SharePoint.Workflow.SPWorkflowActivationProperties..ctor (SPWorkflow workflow, Int32 runAsUserId, String a...

Pocket Excel
Microsoft REALLY needs to support Excel drop down boxes for the pocket PC. Inputting is cumbersome with the Pocket PC and if there ever was an application that where drop downs would be useful it's on pocket excel. I realize some functionality must be sacrificed .... but not drop downs! How about a patch to upgrade? Hi as this is a peer-to-peer newsgroup you may try emailing MS directly: mswish@microsoft.com -- Regards Frank Kabel Frankfurt, Germany "brian" <brian@discussions.microsoft.com> schrieb im Newsbeitrag news:4C8E8833-6EC6-45CC-82FC-6830F2B25FCF@microsoft....

error: end of file reached
Hi, I have Outlook Express 5.02 on the Mac (OS 8.6). I was running Speed = disk=20 on my Mac and it hung. Now, when I try to launch Outlook I get an error=20 message that says: "An error has occurred. The end of file was reached." = and=20 Outlook fails to launch. Per Knowledge base article 296844 I have: 1. Deleted the Identity preferences file. 2. Deleted the Outlook Express preferences file. 3. Rebuilt the desktop. 4. Tried rebuilding the Outlook Express database (by holding the option = key=20 while launching. I recieved the same "end of file" message when I tried = a=2...

Outlook will not open, error 1402
Hi all, My outlook 2003 is acting up. It will do one of two things. Most of the time it will not open(unless I restart the machine) and when it does open after a restart it gives me an error 1402. I cannot find any information on this in the knowledge base and I have already tried to do a detect/repair on the software. Any ideas? thanks, fil ...

Error 2465; Can't Find Field
The section of code below is causing me problems. Private Sub AddBadParts() Dim dbs As DAO.Database Dim strSQL As String Dim MyDate Dim MyTime Set dbs = CurrentDb 'strSQL = "INSERT INTO tblTally ([CatalogNumber]) VALUES (" & Me.[txtCatNum] & ");" strSQL = "INSERT INTO tblTally ([CatalogNumber]) VALUES (""Hello 101st Try"");" dbs.Execute strSQL End Sub ID - is the first field in the table (auto number) CatalogNumber - is the second field in th...

error when send e-mail
Hello, I have problem, when I try send e-mail from CRM. "Unexpected error" message was displayed. In logs in our exchange server I found this message: 2005-03-23 13:31:11 192.168.1.25 POST /MSCRMConnector/ICrmEmailDispatch.SRF - 80 - 192.168.1.26 Microsoft-ATL-Native/7.00 401 2 2148074254 Anybody help me please? Thank you Check the event log on the crm server, there should be an error there that provides come more information. Matt Parks MVP - Microsoft CRM ---------------------------------------- ---------------------------------------- On Wed, 23 Mar 2005 06:47:05 -0800, Fa...

Text in cell
When I fill a cell with text, I end up not seeing all the typed text that shows in the address bar. Even if I make the cell higher, it does not allow for more text. I want to maintain the width I have, but increase heigth if need be. Is the amount of text allowed in a cell predetermined, or can I get more to show in the cell? Have you tried Format > Cells > Alignment > Wrap Text? -Dave "Ted" <tedwoodie@worldnet.att.net> wrote in message news:122401c37e4c$02b386c0$a001280a@phx.gbl... > When I fill a cell with text, I end up not seeing all the > typed text ...

FRX Error 19999
I am getting an Error 19999 "Error reading command line" when I try and launch FRX from within Great Plains. This is only happening on 1 install and I can't locate any information to see what is wrong with that client. I can launch FRx succesfully from Report Designer but not sure why the Report Laucher is not working. Any help would be greatly appreciated. Thanks! ...

one text column to several columns/table ?
Occasionally I would like to print the contents of a big folder onto one sheet of paper. ( TO scribble make notes etc. ) Often it's an entire folder so I type "dir /b > pix.txt". ( remember DOS ? ) That gets me all the filenames in one file that I open with Excel - and of course all the data is in column A. Anyway in Excel to easily convert that into "as many columns as it takes to print onto one sheet of paper" other than me doing some math to figure how many columns I'd like and then cut/paste into B, C , D etc ? Hi Bobb - is there a reason not ...