I have a query based on a query, when I added a new field to the base query, this field did not appear in the list of fields in the design definition when I try and add to the upper query. It just shows the original fields only.
Same thing happened in a field on a report - I had to delete the field and create it again...
I am feeling around my database which was upgraded from Access 2003 to Access 2013. I am getting error messages and I was trying to figure out why a particular query gave an error. I clicked on "data definition" and I think it opened a new query, but it will not let me close it. The message is invalid SQL statement, expected Delete, Insert, Procedure, Select or Update. There is no way to cancel this.
I had to use the task manager to close the database, not what I like to do.
What do you do with "data definition"?
I am developing a calender to display HOTEL room occupancy (past,present) and combine with future "outlook" dates and 1/0 values from active registrations that go beyond the present date.
I am working with MS ACCESS 2007. My problem is ONE SPECIFIC QUERY AND IIF STATEMENT. I want to concatenate some text (using &) along with numbers converted to text (using the CSTr function). I am using iif function and I want to use the full text string as a variable argument to be executed in the iif function. The result of the &concatenate is a text field like [p1] or [p2] or [p#] with numbers 1-31.
But, I do not want the final result as the argument. I want the query and iif to use the string expression written into the argument as the variable argument that can be calculated based on OTHER numbers that change everyday in the daily run of the calender.
The field in the statement [calc number] is a date conversion factor that changes everyday.
I want the iif statement to execute using the string as a variable argument. I am writing only within the QUERY to define the query object. I am not writing into any SQL module or code. My field definition and iif statement is below (calc number changes everyday)
CalDate18: iif(18 greater date();"[p" & [calc number] & "]";0
I do not have greater-than key on this international keyboard !
When I use this in an update query, I get format conversion error. When I use the same definition in MAKE-TABLE query, it gives the resolved value "[p1]" for the value of [CalDate18] ......... that is not what I want.
I want the string to be taken literally and executed. Seems there may be a special character to precede the argument or WRAP the argument such as done in Excel. Example # "[p" & [calc number] & "]" #
I am just querying a single table, no relationship involved with another table. As you can see form the attached jpeg, the ZIP field in some cases is empty. I would run a search using Is NULL but the field is NOT numerical. It's a long story but I had to make this field a TEXT field. Basically, what statement do I have to insert in the criteria field to just pull up the EMPTY ZIP fields?
View 2 Replies View RelatedI am trying to write some fields to an access table definition. It is working for the most part except that I want to write some of the fields as dbText and others as dbDate. For some reason it is writing all of them as dbDate and I don't know why.
Code:
additionalColumns = Array("a","b","c","d","e","f")
'add additonal columns to report
For i = LBound(additionalColumns) To UBound(additionalColumns)
MsgBox additionalColumns(i)
If additionalColumns(i).Value = "a" OR "b" Then
Set columnNames = xlsht.Cells(1, additionalColumns(i))
Set FieldName = tb1.CreateField(additionalColumns(i), dbDate, 10)
tb1.Fields.Append FieldName
Else
Set columnNames = xlsht.Cells(1, additionalColumns(i))
Set FieldName = tb1.CreateField(additionalColumns(i), dbText, 150)
tb1.Fields.Append FieldName
End If
Next i
I am creating a database for my school and I can't create a relationship between two tables. There are only two tables and I need them to be one-to-many however it says there is an 'invalid field definition'. I have attached the database.
View 3 Replies View RelatedIn this thread:
http://www.access-programmers.co.uk/forums/showthread.php?p=549287#post549287
I had a problem. Now I decided to just make two tables, one with sales for a "passer by" and/or directly to a client without a case/labour costs. And a table for sales in relation to a case with labour costs etc. I guess I still m*ssed up on that part and have to make do with that decision (kind of like I depicted in the photoshopped image).
For invoicenumbers I want to use DDMMYYYYHHMM. When adding that field to the design of a table, how do I go about that? Do I just use a Text field and create an input mask for that? Or do I need to use a number format with a certain input mask? Or would it be wiser just to use the date/time type of field and give it a new inputmask? I guess 000000000000 would be the one for that, right? Although I read the help file I do not know if I should use ;0; behind those first group of zero's or not. Yes it is a mandatory field, but it will fill in itself as soon as a product is typed in in the product/invoice list, with a default value of the current date and time.
I need to brush up my knowledge on the formatting for that so that 28-05-1975 19:55 will be displayed and stored as 280519751955. Or is that also done automatically by using that input mask? Or will that give an error when Date or Time() etc. is used for that field in VBA?
Just a few questions I need some help with, I've been trying some stuff but it really doesn't "stick".
And if anyone has some time, I could really use some general advice/ideas for setting up the invoicing part differently for a new version of the db yet to come in the future.
(named: Invalid Field Definition - Unable to set up cascade update but I have done what the moderator advised and have not been able to fix a very similar problem.
I have planned a database and must adhere to the plan or change it and basically I initially said that I would like to enforce referential integrity to synchronise my tables and I would also like to cascade update and delete to save input time because changes will be automated between table (where fields are connected.).
I have attached a plan I did if that makes the purpose of my database clearer.I have attached my database and have established the relationships* without referential integrity enforced I would like between my tables but don't know how to make them one-to-many relationships nor can I enable cascade update or cascade delete or enforce referential integrity.
I am new to databases (and to this forum) and have been promised by my line manager that I won't have to specialise, I don't think I have created the relationships but they are supposed to be between Vehicle ID and Hire Number, and Customer ID and Hire Number.
In my access form I provide the user a list of locations from various countries in a listbox . But the list is too long so I provide him a combobox for selecting a country. Selecting the country should update the listbox showing only the locations in that specific country.
So my SELECT from the listbox must cover the unselected state and show all entries and when a country is selected it must narrow the selection.
I tried to get this happen with the following SELECT statement containing a variable. Choosing a country in the Combobox results in a change of the variable and in a requery. This works after the first country is selected and for each country change, but the initial list is empty.
VBA in the loadform
'Application.TempVars.Add "varcountryselect", "*"
SELECT in the listbox "lstlocationsperproject"
SELECT tbllocations.locationID, tbllocations.country, tbllocations.localstreet, tbllocations.localcity FROM tbllocations WHERE ((tbllocations.country) Like [TempVar]![varcountryselect]);
VBA in the combobox
Application.TempVars("varcountryselect") = [Form]![kombcountryselect].Column(0)
Me.lstlocationsperproject.Requery
The values in [kombcountryselect].Column(0) are texts like "SPAIN", "MEXICO", etc.
Any hints, how I have to use the * for getting the complete list on the initial view ?
how can i make a specific field to be a sum of many other Fields in design table with out using codes
View 1 Replies View RelatedI run a query daily and have to alter the date in the design view (as below).
>=#01/04/2013# And <#11/03/2014#
All I want is for the query to run 2 days behind, so for example today is the 12/03/2014, I use the above, and then daily change this every day.
I am trying to hardcode in the field name. This is what I want to hardcode "TEXT(ROW(A17),"-0")" but the parenthesis and quotation marks are causing a syntax error. It appears like this
IDENTITY:"TEXT(ROW(A17),"-0")"
What am I doing wrong?
I have a query with multiple fields that is being run off of 3 parameters (linked for selection in a form). The problem is, I wanted to enable a select all feature, so I included a "Or ... Is Null" part in my criteria section, so that when nothing is selected, the query/report returns all records.
Okay so the problem is whenever I run the query with nothing selected for the parameter and then return to design view for the query, a new field has been created in the query design, titled with the expression I use to pull the parameter value from the form. This is frustrating because then that is causing errors in another report I run that pulls values from that query.
I have had to use my first crosstab queries.
I now understand that when opening and saving crosstab queries Access (2010) runs that query to ascertain the column names. Unless you hard-code them. Running the query takes at least 20 minutes.
I have hard-coded where I can, but one report takes arbitrary dates so I can't hard-code them.
I believe that turning off AutoCorrect might make a difference to whether the query runs - but I don't want to turn it off.
I’m developing a claim tracking database that tracks dates of events that occur in the course of processing a claim; such as, Loss Date, Report Date, Estimate Date, Payment Date, etc. There are 16 different “Events” in all.I currently have the following tables set up:
tblClaim
ClaimID
ClaimNumber
fkEmpID
tblEmployee
EmpID
EmpName
[code]....
What I need to do is create a form where management can choose two or more events, and calculate the average number of days between two of any of the events, for an employee, or all employees.I have created a crosstab query to change the values in the EventName field in tblEvents to field names, and the EventDate as values for the related EventNames. I created another query based on this query to do the DateDiff.
I created combo boxes on my form with the Row Source Type set to Field List, for a list of fields in my crosstab query. I’ve tried to use the following DateDiff function to get the days between the two fields selected in my combo boxes:
Code:
DateDiff("d",[Forms]![frmReportBuilder]![cboEvent1],[Forms]![frmReportBuilder]![cboEvent2])
But I get an error about unrecognized field name or expression for my combo boxes. So I added my combo boxes in the query parameter window, with a data type as both text and value, but with both I get an error “This expression is typed incorrectly or is too complex to be evaluated.” I also specified the column headings in the crosstab but I still am getting the “too complex” error.I’m pretty sure it’s trying to do a Datediff on the literal values in the comboboxes and not recognizing that I’m trying to specify field names.Is it possible to assign field names in DateDiff this way?
I have a form based on a table and I just added 3 fields to the table. When I go to the form to add these fields, they do not show up in the field list. I've also tried creating a textbox and going to the Data Tab and choosing the data source but those 3 new fields are still not showing up.
How can I go about adding these 3 fields?
Thanks in advance
How can list the contents of one field columns in one row in a report.
Example:
ID: 1
A1
A2
A3
A4
Should be: ID1 - A1, A2, A2, A3, A4
I have no experience with Access Modules which I think I need to find all the queries that reference a field, in this example: LAWSON_LHSEMPDEMO.R_STATUS
I would like for the module to list all the queries for me that reference this table and field of "LAWSON_LHSEMPDEMO.R_STATUS"
Is there an easy way to do this?
695515*97
531924*11
495853*131
377035*181
Is there a way to extract the 97, 11, 131, 181 numbers from the above list in a separate field?
On [Form1] I have a Multi-Select Listbox[List1] which shows Job Numbers [WBS] (The job numbers displaying are a result of a separate query filtered by the Fiscal Year combobox). When I click [CMD1] I want [Query1] results to only be selected [WBS] from the form.What is the best way to code this? I'm a beginner when it comes to VBA.
View 12 Replies View RelatedI have to design a price list table which is currently done in Excel.
The table looks like this :
[Headers]
Product Group | Layers | (this are quantity bands) 0-100 | 101-500 | 501-1000| etc
Test1 | 2 | £10(normal) £9(special)
The quantity band currently is fixed to 5 bands but would need to be flexible. There are also 2 prices for each of the quantity band (normal/special)
At the moment my table design looks like this:
ID
fkSupplierID
fkProductGroupID
txtLayers (value list)
intMinQty
intMaxQty
curNormalPrice
curSpecialPrice
This works quite well with the query to return price based on product group, layer and order qty. However I am not very sure if this is the best way to design this. I am just thinking about maintenance - for example when the supplier puts in a price change or when the quantity band changes. The current format (quantity band) is based on a major supplier but in the future we would like to adopt this for any supplier.
I have a table x where the field value is selected via a combo box in a form that is populated from another table z.
When I look in x, it appears to have correctly stored the text from z, not the ID number.
I then built a Query, qX, which looks in x and grabs the fields I want. That query shows the text correctly in each selected field.
Now when I build a form, frmQx and use a List control, it displays some of the fields as ID values from the original table z, not the text values.
How can I get round this? I've searched and searched for an answer, sigh. Maybe I'm just not quite certain how to phrase the search.
My computer has been updated to 2010 whilst I've been off sick (was 2003 before my accident).
I've created a main table, for devises across the company, and a combo box/selection box based on another table which holds a list of all the "Responsible" employee's aswell as another combo box/selection box for the device location.
So the person entering the information, can enter all the information for a device (torq wrench, socket set etc), who is responsible for it and which department they belong to (where to find the device).
Which all works fine
However, I'd like to create 2 queries, one to enable the user to run a report of all device's allocated with an employee or to be able to run a query for all device's stored in a particular department.
But I have been unable to set the correct query criteria, to enable to query user to be able to selection from a drop down list, which responsible person or location to pull back the correct list.
I was getting an error asking me to set the parenthesis, I have now deleted criteria for both queries, as even if I put [Enter] and type a Responsible person's name exactly as its held on the table, the report comes back blank.
I have a combo box in my report design and I would like it to display "true" values as a list with check boxes.
View 5 Replies View RelatedI generally use Cognos for my queries, so the overly user friendlyness of access is confusing me and im not proficient enough in sql to just go in and edit the code...
How do i generate a query that groups data in unique ways in the result, but does not change the data in the originating table.
For example:
I have the column tbemployment.dept . In a crosstab query i want everybody that isnt tbvoter.dept='BELLS' to be counted as 'other'.
In Cognos i would do this in the 'data definition' window for that field and use 'if (tbvoter.dept<> 'BELLS') then ('other') else (tbvoter.dept)'
How do i do this in access?
I have a table that contains Investigator1,Investigator2 and other fields
And another table that contains PID, Rank_Name_Family and other fields
I am making a query in that query i want to get the names of Investigator1 and Investigator2
Investigator1 and Investigator2 is the PID
for example
Investigator1 = 1
Investigator2 = 2
PID = 1 Rank_Name_Family= Roy Jalbout
PID = 2 Rank_Name_Family= Rony Jalbout
i tried this two solutions but the result is error
Solution 1
Code:
Name1: DLookUp([Rank_Name_Family];"tbl_Personal_Information";[tbl_Personal_Information].[PID]=[tbl_Driving_Expert].[Investigator1])
Name2: DLookUp([Rank_Name_Family];"tbl_Personal_Information";[tbl_Personal_Information].[PID]=[tbl_Driving_Expert].[Investigator2])
Solution 2
Code:
Name1: DLookUp([Rank_Name_Family];"tbl_Personal_Information";[tbl_Personal_Information].[PID]=[Investigator1])
Name2: DLookUp([Rank_Name_Family];"tbl_Personal_Information";[tbl_Personal_Information].[PID]=[Investigator2])