Reports :: Database Engine Does Not Recognize Payment As Valid Field Name Or Expression

Feb 27, 2015

I have a small database for producing various financial reports, by date period (from/to). It works perfectly except when there are either no payment records or no receipt records for the chosen period. Naturally enough, MS Access comes up with the message "Database engine does not recognize 'payment' as a valid field name or expression" --- !!!

Is there some way I can tell MS Access that I don't mind if eg the payment column result is zero?

The structure of the table on which the report is based (via a crosstab query) is :

transaction date
auto number ID
transaction type (either payment or receipt, chosen by form's drop down box) - TEXT
amount - CURRENCY
receipt type - TEXT
payment type - TEXT
fundno - TEXT

The crosstab query design is as per the attached jpeg file

View Replies


ADVERTISEMENT

Queries :: Not Recognizing Expression As A Valid Field Name When Applying Data To A Chart

Jun 18, 2015

So I'm new to Access, and I am trying use a query that can be referred to by a chart. So the idea is that I use the query to select data only from the date range that the user chooses on the home screen of the database for their chart (using the command Between [Forms]![Home Screen]![From] And [Forms]![Home Screen]![to])..Although it has been working fine for charts that only have two parameters, when I attempted to make a line graph that sorts by 3 parameters (i.e. date and amount for different types of something), it stops. I get the message that "The Microsoft Office Access database does not recognize [Forms]![Home Screen]![From] as a valid field name or expression" or something to that effect.I'd rather not remove the whole specification created by using the dates from the home screen, as it has been working fine on all other aspects of my charts and reports.

View 9 Replies View Related

Reports :: Adding Another Expression To Database

Jul 21, 2013

in my daily roll call report i have 2 groups..."on program" and "graduates" these 2 groups are creating in the query...as u see in the 2nd pic... the expression as followed

Expr1: IIf([Date Graduated]<Date(),Date(),[Date Graduated])

next you can see in my 3rd pic the report and the expression that gives the 2 groups there names...it is as followed

=IIf(IsNull([Date Out]),IIf(IsNull([Date Graduated]),"On Program","Graduates"),"Recent Departures")

i will clarify that i took out the names in the roll call but both groups are sorted by the date they came in going down the list..now i need to add another group "Staff Members" to my roll call.every way i have tried altering the query expression or the report expression result in a blank roll call.

View 14 Replies View Related

Queries :: Crsosstab Query Doesn't Recognize Expression In Source Query

Aug 12, 2015

I have a query where these are the fields:

ProductRevType
RevLag
RevFlowThru
CloseMoYr
ProjRevDate
CurrentMRC
ProjRevMRC

The ProjRevMRC field is an expression that reads:ProjRevMRC: IIf([ProjRevDate]>=DateSerial(Year(Date()),Month(Date()),1),[CurrentMRC]*[qry303a_ SFADetailMRC_ONLY]![Rev Flow Through],0)

When I run the query, it works perfectly, but when I created a crosstab query to show totals by month, I wanted the totals to be zero for the months less than the current month. Is there a way for the crosstab query to execute the expression and put zeroes for those months?

View 4 Replies View Related

Reports :: Gross Payment Generation - Group Sub Totals

Aug 14, 2014

I have a database where my team will enter manual payment calculations into. Once entered, they will run and print the report for actual payment.

The report I have groups by payment type (see attached image of paymetn types) and then sub totals by group.

I need to somehow get these totals and use them to generate a gross payment. In the attached example, the gross would be the sum of worked hours + before tax allowance + after tax allowance. I'm not sure how I can do this in the group footer.

I have attached images.

View 1 Replies View Related

Reports :: Expression Builder - Sum Fields If Another Field Is Less Than 30

May 21, 2014

I am trying to sum fields if a another field (Accurate/Complete (30 pts)) is less than 30

Right now it works to provide my total if the yes/no button for a certain field has been clicked.

=Sum(IIf([Procedure Error],1,0))

now I want it to only total the numbers if

[Accurate/Complete (30 pts)]<30

How would I add the if less than to the top expression?

View 3 Replies View Related

Reports :: Creating Field Using Expression Builder

Dec 18, 2014

I am using expression builder to specify a field in a report but it is acting more like a filter.So I have a report based on a query. However I want to add a field that is not in the query but is in a related table - called tblAgent.

So using expression builder I select the tblAgent in Expression Elements and then select the field from this table. This creates the expression =[Agent]![AgentAddress] however when I try to run the report it asks for a Agent parameter? Do I need to go back to reports 101?

View 1 Replies View Related

Reports :: Image Visible On Sub-report When Choose Received Payment In Subform

Mar 4, 2014

I have a database that I use for keeping track of clients and printing invoices using a form/sub-form and report/sub-report. I want an image to be visible on my sub-report when I choose Received Payment in my sub-form. Right now I have my image set to visible = no.

View 14 Replies View Related

General :: Order Payment Database - Balance Due

Nov 22, 2012

I am new to Access. I am creating a simple order/payment database.

I have tables for the orders and payments and have a relationship setup.

I created an order form with a subform for the payments.

[URL] .....

View 2 Replies View Related

Modules & VBA :: How To Recognize Empty Field

Sep 24, 2013

Why can't I get this simple piece of code to work?

Code:
Private Sub txtDateBack_AfterUpdate()
' updates loan status
txtLoaned = 0
If txtDateBack = Null Then txtLoaned = 1
End Sub

Code:
Private Sub txtDateBack_AfterUpdate()
' updates loan status
txtLoaned = 0
If txtDateBack = "" Then txtLoaned = 1
End Sub

View 4 Replies View Related

Jet Database Engine Error On Non-shared Database

Jul 7, 2005

I have a problem that seems to be happening on several users' databases and is causing a big problem. None of the databases is a shared database...they are all single-user databases on stand-alone computers. I have tried looking for help within previous posts, but all seem to be related to shared databases.

I am getting an error message: "The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time." The database cannot be opened, imported, repaired...nothing seems to work.

Again...these are NOT shared databases. I appreciate any help I can get. I created the database for all of the secretaries in our school district to keep up with absence data. It involves many tables, queries, forms and reports, and has generally worked well. However I am now seeing several that are getting similar errors as mentioned.

Thanks!

View 3 Replies View Related

General :: Student Database - Transfer Of Payment To New Semester And Session

Nov 18, 2012

I am creating a student database in ms access2007 but finding difficult to go further. In my database, I have created the following

Student-info-table
SemesterTable
SessionTable
PaymentTable

Then a query from the payment that shows student I'd,arrears,amount due,paid. And balance

My question is

1.How do I transfer the students and their balance to a new semester and session
2.Do I have to new student and payment tables every semester bcos payment are made every semester
3. I want to keep each students payment for as long as they remain in the school
4. A session is made up three semesters how do I transfer students to a new session

May be my tables layout are faulty...

View 1 Replies View Related

General :: Payment Database - Monthly / Annual Renewal Popup Message

Mar 20, 2013

I am creating payment database for a community gym, the data will be created on a data sheet.

There are 2 payment types monthly and annual (these are on a dropdown box with their own id number).

I have a joining date and a paid date, but reflected against the payment type ie 31 days or 365 days.

I have been trying to do something along the lines of:

if [monthly] (is selected) (so this would call up a 31days) date() is > [joining date] then give message to renew and same kinda thing for annual.

View 1 Replies View Related

Not A Valid Bookmark When Opening Database

Feb 6, 2008

Hi,

I tried to search the forums for an answer but I still can't seem to solve the problem.
I receive the "Not a valid bookmark" error message when attempting to open my database. I have tried the compact tool through access and also jetcomp but it just brings up the same error message on access and says "error compacting database" on jetcomp. Can anyone provide a solution to my problem in order that i can either open the database or else retrieve data from it?
Many thanks, any help appreciated
Jon.

I have attached the database in case you have any software which might be of use.

View 1 Replies View Related

Microsoft Jet Database Engine ERROR

Jul 7, 2006

Hi,

We have been having intermittent problems with an MS Access 2000 front-end application linked to a SQL Server 2000 database recently.

From the switchboard a user was sometimes getting "There was an error executing the previous command". When she shut the application and opened it again it works fine for a little while - then the error occurs again.

I removed the generic error handling code from the code for the Switchboard form and I got the error message: "The Microsoft Jet database engine cannot find the input table or query 'Switchboard items'. Make sure it exists and that its name is spelled correctly."

I have been searching this forum and the web to find out what the cause of this error message is but I cannot find anything to go on or to try that might help get rid of this problem.

Does anyone know why this error occurs? Any idea how to stop it of happening?

Any ideas or suggestions would be greatly appreciated.

View 1 Replies View Related

The Microsoft Jet Database Engine Not Rocognizing

Dec 14, 2006

Start with: frm_W-GraphSearch
select from combo and ever this for date criteria: 12/1/06 to 12/13/06
Open report - There you will see one out of three graphs showing
Go to Report Design mode and open the sql in one of the graphs and try to run it, there you see the error:

"The Mocrosoft Jet engine does not recognize 'Forms![W-GraphSearch].Text0' as a valid field name or expression."

Anyone have any ideas?

Thanks again in advance for your help,

Kilch

View 13 Replies View Related

MS Jet Database Engine Stopped Process ...

Aug 14, 2007

Dear All,
I prepared times ago a database that contains important data that will be frequently updated.
Since yesterday I cannot load the database anymore. I get a pop-up with following statement:
Quote
The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time
Unquote
If I click ok, the loading process will be aborted.
I'm the only user of this database, neither the database nor the directory containing the database is sharable. It is located on the harddisk of my computer which nobody from outside can access.
What can I do to recover the access to my database?

I use MS Office 2003 but tried to open the database also on MS Office 2000 with the same result.
The Help function of MS-Access does not really help.
Who knows how to solve the probleem????
With regards
Siegfried

View 2 Replies View Related

Keep Getting Microsoft JET Database Engine Error

Oct 13, 2004

i get this error the time i enter info into a registration page from where i am getting all the customers info

Microsoft JET Database Engine error '80004005'

The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.
in line 74
and my corresponding query is

Set Commrs2 = Server.CreateObject("ADODB.Command")
Commrs2.ActiveConnection=strconnect
line 74: Commrs2.CommandText = "INSERT into registration(username,fname, mname, lname, sex, address, city, state, country, pincode, phone, mobile ,email)VALUES('"&logid&"','"&fname&"','"&mname&"','"&lname&"','"&sex&"','"&add&"','"&city&"','"&state&"','"&country&"','"&pin&"','"&phone&"','"&mobile&"','"&email&"' )"
Commrs2.Execute

Set Commrs3 = Server.CreateObject("ADODB.Command")
Commrs3.ActiveConnection=strconnect
line 79 : Commrs3.CommandText = "INSERT into userpass(username,password) VALUES('"&logid&"','"&pass&"'"
Commrs3.Execute

also sometimes i get this on line 79
Microsoft JET Database Engine error '80040e14'
Syntax error in INSERT INTO statement.


i did a google search and i found this article
http://www.kbalertz.com/kb_884185.aspx
but i was not able to find the security warning dialogue box anywhere

i am using an access 2000 format database

any more info req plz let me know
thanks

View 5 Replies View Related

Microsoft JET Database Engine (0x80004005)

Apr 5, 2005

I am trying to access a Microsoft Access database located on a server on my network from my web server. The folder containing the access database has been shared on the network with everyone having full access. I created a virtual directory on my web server pointing to the share on the other server and when I try to connect it says:

Microsoft JET Database Engine (0x80004005)
The Microsoft Jet database engine cannot open the file '\server1databasejt_test.mdb'. It is already opened exclusively by another user, or you need permission to view its data.

The code to open look like:

Set objConn = Server.CreateObject("ADODB.Connection")
dbPath = server.MapPath("/Database/jt_test.mdb")
connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";"

I have tried everything I can think of to get this to work and nothing works. Any ideas?

View 1 Replies View Related

Web Database Expression - Number Of Days Between A Field And Todays Date?

Jul 9, 2012

How do I write a Access 2010 Web database expression to give me the number of days between a particular field eg [sold date] and todays date?

Being a Web database I know I am restricted to a smaller list of available expressions - normally part of my expression would include eg date().

View 3 Replies View Related

General :: Split Database With Not Valid Path

Nov 6, 2013

I am attempting to split an Access 2007 database. My company has two locations. From my location we are remote connecting into the server. While down there they are connecting directly. When I split the database, people in my location can use it fine. When people down there use it, they get a not valid path error.

This is because the network drives are mapped differently. I have been reading that the solution is to use the UNC for the back end file path.

View 1 Replies View Related

Unable To Find A Valid Switchboard In Database

Jan 10, 2014

Here's the issue:
Access 2010 Database with a Switchboard
SharePoint 2007

After I moved the tables to the SharePoint site everything is working correctly except the Switchboard Manager. When I try to the Switchboard manager i get error that is was unable to find a valid Switchboard in the database and asks me to create one. If I click Yes I get error that Table 'Switchboard Items already exists.

My Database has the following:
1. Switchboard Items Table
2. Switchboard form

I can access both of them and attached screenshots of the errors and tables in the database.

View 1 Replies View Related

Error Message: Microsoft Jet Database Engine

Oct 19, 2006

I am trying to make changes to a particular field in Access but whenever I try to do this, I get an error messsage saying that Microsoft Jet Database Engine Stopped the process because you and another user are attempting to change the same data at the same time.

There is no once accessing the database at this time and this error message appears only when I go to that specific field. I've also tried to delete the whole row but it wouldn't allow me to do that saying the program has been locked.

Any solutions? Thanks,

Rajiv

View 4 Replies View Related

Microsoft JET Database Engine Error '80004005'

Oct 7, 2004

I have a recursive script that seems to timeout after a few dozen requests, the database is returning the typical "Microsoft JET Database Engineerror '80004005' " error ("Unspecified error").

The code is pretty straight forward, it is a function that calls itself to generate a tree structure for a site map. It works great the first few dozen times but seems to 'time out' and return the above error after a few dozen records (building the tree).

The code looks like this :

Function BuildContent(id,depth)
' open the children
Set GetKids = Server.CreateObject("ADODB.Recordset")
GetKids.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=d:usersXXXXhtmldb1.mdb"
GetKids.Source = "SELECT ID,Name FROM category WHERE parent = " & id & ""
GetKids.CursorType = 0
GetKids.CursorLocation = 2
GetKids.LockType = 1
GetKids.Open()
GetKids_numRows = 0


if GetKids.EOF <> true then
MyLinks = countlinks(id)
MyCats = countkids(id)
response.write "Number of Listings (" & GetKids("Name") & "): " & Mylinks & "<br>"

maxdepth = 1
BuildContent = "<ol>"
while GetKids.EOF <> true

KidsCats = countkids(id)
KidsLinks = countlinks(GetKids("id"))

if MyCats > 0 then show = true
if KidsCats > 0 then show = true
if depth < maxdepth then show = false

if KidsLinks > 0 then show = true
if MyLinks > 0 then show = true

if show = true then
BuildContent = BuildContent & "<li><a href='" & GetKids("id") & "_e.asp'>" & GetKids("name") & "</a></li>"
else
BuildContent = BuildContent & "<li>" & GetKids("name") & "</li>"
end if
if depth < maxdepth then BuildContent = BuildContent & BuildContent(GetKids("id"),depth+1)
GetKids.MoveNext
wend
GetKids.Close()
Set GetKids = Nothing

BuildContent = BuildContent & "<br></ol>"
end if
End Function

there are a couple of support fuctions :

Function countkids(id)
Set GetListing = Server.CreateObject("ADODB.Recordset")
GetListing.ActiveConnection = MM_Connection_STRING
GetListing.Source = "select * FROM category WHERE parent = " & id & ""
GetListing.CursorType = 1
GetListing.CursorLocation = 2
GetListing.LockType = 1
GetListing.Open()
GetListing_numRows = 0
countkids = GetListing.RecordCount
GetListing.Close
Set GetListing = Nothing
End Function

Function countlinks(id)
Set GetListing = Server.CreateObject("ADODB.Recordset")
GetListing.ActiveConnection = MM_Connection_STRING
GetListing.Source = "select * FROM records WHERE parent = " & id & ""
GetListing.CursorType = 1
GetListing.CursorLocation = 2
GetListing.LockType = 1
GetListing.Open()
GetListing_numRows = 0
countlinks = GetListing.RecordCount
GetListing.Close
Set GetListing = Nothing
End Function



I think the DB is being called too often, too quickly but I have no way of slowing the script down. If that's the case, is there a way to slow the code execution to give the DB time to catch up?

Otherwise, is it a driver problem (Microsoft JET Database Engineerror '80004005' ) and would switching to a OBDC connection give me better results or should I be using a different db driver?

Any help would be greatly appreciated.

View 4 Replies View Related

Microsoft JET Database Engine Error '80040e14'

Mar 16, 2005

I'm getting this error
Microsoft JET Database Engine error '80040e14'

Syntax error (missing operator) in query _expression '123 street'.


The field in DB is type text. But the data to be written in the field is; number followed by characters. In other words it is an address field on the form, trying to insert new record into DB.


here is 3 different data I entered into field:


1. 'just some text' - works fine
2. 'text and number 123 ' - works fine
3. '123 number and text' - does not work. and gives the above error.
Anybody has any idea?

View 2 Replies View Related

Microsoft JET Database Engine Error '80004005'

Oct 19, 2004

I have an annoying error that i cant get round... the error i receive is:

Insert into TSContacts (CompanyId,FirstName,LastName,ThereMessage,JobTitl e,UserName,[Password]) Values(0,'','','','','','')


Microsoft JET Database Engineerror '80004005'

Field 'TSContacts.FirstName' cannot be a zero-length string. /vip_admin/AddContact.asp, line 55

dont really know whats going on here....

this is the addcontact.asp code:

'execute insert command
Dim strUpdate
Dim strFirstName,StrLastName,strLoginName,strPassword, strMessage,strJobTitle
strFirstName ="'"&Request.Form("strFirstName")&"'"
strLastName ="'"&Request.Form("strLastName")&"'"
strLoginName ="'"&Request.Form("strUserName")&"'"
strPassword ="'"&Request.Form("strPassword")&"'"
strMessage ="'"&Request.Form("strMessage")&"'"
strJobTitle ="'"&Request.Form("strJobTitle")&"'"
nCompanyId =CDbl(Request.Form("strCompanyID"))

strUpdate="Insert into TSContacts (CompanyId,FirstName,LastName,ThereMessage,JobTitl e,UserName,[Password]) Values("
strUpdate=StrUpdate & nCompanyID
strUpdate=strUpdate & ","&strFirstName
strUpdate=strUpdate & ","&strLastName
strUpdate=strUpdate & ","& strMessage
strUpdate=strUpdate & ","& strJobTitle
strUpdate=strUpdate & ","& strLoginName
strUpdate=strUpdate & ","& strPassword &")"
Response.Write(strUpdate)
dbServer.Execute(strUpdate)
if err then
'if error stop
response.write err
else
Response.redirect "customercontacts.asp?CUSTID="&nCompanyID
end if




'dbServer.close
'dbserver=nothing
%>
<body>
<p>&nbsp;</p>
</body>
</html>

can anyone help - its been driving me up the wall!

thanks,

butain

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved