This outputs smth
SELECT Proj_Name & '-' & Proj_ID as Emp_Pr from Emp_Prj where trim(Proj_Name) like trim('Dot')
------
But not this...why ??? I am using ms access
SELECT Proj_Name & '-' & Proj_ID as Emp_Pr from Emp_Prj where trim(Proj_Name) like trim('%Dot%')
Ok here is the deal
You start to type with a smile on your face
Form_MyForm. <-- from that point (literaly) you usualy have a list of things you can do with that form.
All i wanted to do is a requery but i have no list...and even if i type myself Form_MyForm.requery .. this will not work cause it says that it cant find the dang form.
I have tried renaming the form, let someone else type the name for me, copy&paste ..nothing work, when i get to the point...it never gives me a list of things i can do with that form.
what is the deal here..anyone ?
Wow that felt good only to talk about it
Hello, Something weird happened to me - I have a form with continuous subforms and on it are checkboxes. When I click on one specific checkbox, a text box on the parent form becomes visible, where I can add text, which is added directly to the table in the AfterUpdate event. However, the query underlying the subform does not reflect the change, and when I either go to a different record on the parent form, or click on another checkbox in the subform, I get an error message:"This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made. [...]"I can then Save The Record, Copy To Clipboard or Drop Changes.When I drop changes, the text shows up in the query, although my textbox disappears.The one time I selected Save, however, the table got messed up and a foreign key was replaced by what looks like random number [used to be "1", is now "4587525"]. A second foreign key was also changed.Can anybody explain this?And, more importantly, suggest a way to avoid this entire situation? Thank you.
View 1 Replies View RelatedHi All
I have just noticed some strange behaviour in access. I hava and asp page which gets two dates which are then used in a sql statement to refresh a page. The end of the statement deals with a between clause like this
WHERE tbl_Movements.date_of_Movement Between #12/03/2007# AND #30/03/2007#
But when I view this in design mode it changes it to
Between #03/12/2007# And #30/03/2007#
Why does it do this and what can I do to resolve it
Thanks
Hi,
i have a form which is acting strange. i press a button that runs a macro and opens a second form based on my PersonID. ( opens the form showing that person only ). it works great for the first 2 table records. after this, i get a blank screen. not even any text boxes. i have a "where" condition which reads on the following macro sequence:-
OpenForm ( MakeYourPayment )
View ( Form )
Filter Name ( )
Where condition (="[PersonID]="PersonID] )
Data Mode ( Edit )
Window ( Normal )
my second form has PersonID field available so as i read it, my where condition should open the form using Person ID from min form to PersonID on the 2nd form.
any help greatly appreciated as there is a lot of work on this form.
many thank,
Nigel
Hi,
slightly related to a previous thread of mine. thanks to boblarson and co, i have created a subform on my main form that is blank. then i press a button and the sourceobject is set to the desired form i want to have visible. i have the following code-
Private sub Button_Click()
me![MainFormSubForm].sourceObject = " Edit Person" ' name of form
me![MainFormSubForm].LinkMasterFields = "PersonID" 'field on main form to link from
me![MainFormSubForm].LinkChildFlields = "PersonID" ' field on subform to link to
i can get 4 out of 5 forms to link correctly. i have a listbox on my main form that changes the mainform data to show a different person.with the other forms when they are visible, i can click a different person in the listbox and all info changes. form number 5 "Edit Person" doesnt work. when the form is loaded via the above method, it shows 1 record which is the first in the query that the main form is sourced to. when i click another person in the listbox, the subform goes to a (new) record and does not select the person clicked. the main form changes but not the subform.
why does it do this on one form when 4 others work with the code? the same code is method is used.
regs,
Nigel
After working great for 12 months my db has suddenly stopped working tonight! after opening on to the first form and clicking off any field I get "Invalid Data Format" error message pop up and transfered to the code windows. Access then tells me it does not recognise the code and shuts down !!! Has anyone got any ideas PLEASE, this is a disaster
View 14 Replies View Relatedi have an Address textbox that has key behaviour as - new line in field. this allows me to put the complete address in one box, post code is another textbox. this is fine if i want to create window envelopes but - if i want the address to look as one long line in a report it wont work
since the address in the form takes up two lines -- only the first line shows in the address column of the table - hence only one row shows in the report that derives from that table SO =[Address]&" "&[PostCode] placed in the Source Code of the address only shows the first line plus post code . if i make the box deeper then it will show - but only as a second line - i want it all to be one line
can i get out of this and still keep the -new line in field- behaviour?
thanks in anticipation
This is for information only - I am not seeking a solution.
I created a Select Query selecting only two memo fields from a single table.
The query works correctly unless you select Unique Values = Yes which results in the error message "The field is too small to accept the amount of data you attempted to add. Try inserting less data."
Hi. I have a question prompted by database behaviour that I do not understand.
I have two tables with related records, one being a subset of the other. I have joined the tables in a query with a Left outer join relationship. I have put the query results in a form, and when a User clicks a Yes/No box, a new record is created in the subset table automatically generating an ID number using an Autonumber set to start at chosen integers.
However, recently error has occured. When I view the query results some records will not allow me to create a record in the subset table stating "You can not add or change a record because a related record is required in table x", eventhough that very record is the record which the query is displaying. Some records will. The behaviour is erratic and I cannot see what is causing it. Might this be indexing related?
Any help appreciated as ever
Matt
Hi,
I am using a form with 3 combo boxes name, weight and grade. The combo boxes are cascading:
A name is selected in the first combo box, since each name has different weights attached to it only the weights associated with that name are selected when the weight combo box is clicked. In a similar vein, there are only certian grades associated with a certain weight.
The effect is to filter the results so that an invalid entry cannot occur.
My problem is that once an entry is made and I select a new name for the next entry, the weight and grade boxes remain with the same values instead of updating to new values. Updating of the boxes does not appear to be working.
Does anyone have any ideas how to solve this problem?
Many Thanks
Turbojohn
Why does my sub form which is contained in a Tab, not display unless a certain field is populated with something? It's not a required field. Infact it is one of two identical fields... a relationship and a lookup to another table. It doesn't matter if the second one if filled in or not. Just this one. The record is in the database, I choose from a drop down in the header... and the subform then requeries and displays the information. However, unless this 1 field is filled in.. the subform remains blank. It is getting on my nerves!!
View 1 Replies View RelatedI have a table tbl_Fixtures made up of:
fixture_id
team1_id
team2_id
...
...
...
A team id can appear in either team1 or team2 (depends on how the algorithm to compute who plays who calculates it).
I want to know how many games a team plays, so I wrote 4 queries:
qryTeam1Games:
SELECT tbl_Fixtures.team1_id AS team_id, Count(tbl_Fixtures.fixture_id) AS games
FROM tbl_Fixtures
GROUP BY tbl_Fixtures.team1_id;
qryTeams2Games:
SELECT tbl_Fixtures.team2_id AS team_id, Count(tbl_Fixtures.fixture_id) AS games
FROM tbl_Fixtures
GROUP BY tbl_Fixtures.team1_id;
qryAllGames:
SELECT *
FROM qryTeam1Games
UNION
SELECT *
FROM qryTeam2games;
qryNumGamesByTeam:
SELECT qryAllGames.team_id, Sum(qryAllGames.games) AS SumOfgames
FROM qryAllGames
GROUP BY qryAllGames.team_id;
Unfortunately, if the same team_id and games combination appears in both qryTeams1Games and qryTeams2Games, it doesn't bring 2 records into qryAllGames and the summed number of games is only half of what it should be.
Is this a case where a union query shouldn't be used? Is that how it is supposed to work?
A work around isn't a problem but this did have me stumped for a bit.
Hello,
I am hoping someone might be so kind as to help me with a search form I am working on. I have included a test database that has the form. The form has a search box that works dynamically to show results based upon a string search as typed into the search box. The problem is: if I type any spaces beyond the last digit of what I am searching for it no longer displays the intended result.
For example; there is a field called - serial - in the test databse you will see a value in that field of 319-04. If I type 319-04 it shows that record only. However if I key in any additional spaces after the 319-04 it will not show the result.
This is just a problem because I am trying to input the data using a scanner. I have found that if I scan the barcode with the serial# it seems to include two spaces after the scan. I don't know why that is...but rather than worry about that I would just like to try and get the form to display the record even if there are spaces after the last digit entered.
Thanks!
Access 2003 -
I have many queries in a DB. they work fine, but *sometimes* some of my queries are wiped - when i try to run them they say "query must contain at least one cell" or something..
when i check the query then, its empty!
I have saved out the SQL code into a word doc because this has happened a few times now.. so i just copy and paste the query back in - but this is very inconvenient! what is going on?
I have 2 unbound boxes in the footer of my continuous form. Both total up separate sets of fields using DSum and do it correctly. I have used Conditional Formatting on Total1 to make the numbers turn bold and red whenever Total1 is greater than Total2.
Here comes the weird part. It will always work whenever Total1 is greater than Total2, but sometimes when Total2 is bigger, Total1 is still red, even after I attempt to modify it and it requeries, or I go to a different record and come back. Any thoughts??
Thanks
-Jason
I have a weird issue going on...I'm running a "Find Duplicates" query on one of my tables, I'm only checking for duplicate values in 2 fields and outputting a third. now this runs fine, unless I use the total's button to group them. When I group them that access instance stops responding. The reason I'm doing this is the third column can have duplicate values, for my purposes it doesn't matter, but I need to be able to see it.
I'm running this against 120k records.
Anyone help a poor man out?
Ok, a friend of my was on my laptop and he crashed the hardrive. I was able to get the database I have been working on off the computer but when I use my computer at work to try to open it, it opens but says
"Function is not available in expressions in tabel-level validation expresions"
on all the date-time functions. I can't save anything and my queries are not running because of this date/time thing. Anybody know what is going on?
I modified a form in an existing split database, by simply adding several fields. Now, I get various messages such as " not Access database "
or if it opens, the following message when I try to close it:
"Your last change may not have been changed because an internal buffer was locked by another user... "
Plus, when I do get out, I cannot delete the .ldb file which it says is open by Administrator. The thing is I ma only one to try this new database and I set no permisasions or... ?
Please, any ideas?
Russ
sorry,
there are too many requests and not enough information to easily create the required reports for my customers.
I want to copy a table/query with yes/no fields and paste into excel with the
boxes for the yes/no fields. . .
I can't remember or figure out how to do it. . .
I know it can be done as I have done it before, how, i can't recall. . .
thanks
sportsguy
:insert idiot emoticon:
Hi, I have a table with two fields: CustomerName and OrderNumber.
Some order numbers have dashes, for example:
123-4, 123-5, 123-6 etc.
I wrote a query to strip those dashes from these ordernumbers, but only for 3 specific customers: Mark, Mike and Jane.
This is the query:
SELECT CustomerName, OrderNumber,
IIf(InStr(1,[OrderNumber],"-")<>0 And InStr(1,[OrderNumber],"-B")=0,
IIf([CustomerName] Like '*mark*' Or [CustomerName] Like '*mike*' Or [CustomerName] Like '*jane*',
CStr(Mid([OrderNumber],1,InStr(1,[OrderNumber],"-")-1)),
CStr([OrderNumber])),
CStr([OrderNumber])) AS NewOrderNumber
FROM Test
GROUP BY CustomerName, OrderNumber,
IIf(InStr(1,[OrderNumber],"-")<>0 And InStr(1,[OrderNumber],"-B")=0,
IIf([CustomerName] Like '*mark*' Or [CustomerName] Like '*mike*' Or [CustomerName] Like '*jane*',
CStr(Mid([OrderNumber],1,InStr(1,[OrderNumber],"-")-1)),
CStr([OrderNumber])),
CStr([OrderNumber]));
This query works great when I run it from Access. However, if I run it from Visual Basic .NET, it does not strip any dashes for any customers. I am using the following connection string:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C: est.mdb
And the following query:
SELECT * FROM [Strip] ORDER BY NewOrderNumber
(Strip is the query name in Access)
Did anyone encounter a problem like this before?
Thank you,
Nader
hi all,
i have .adp projects.I am facing a problem for which i dont find any reason.whenver i open 2 .adp projects they hang up.I have to kill the msaccess.exe
It okay if i m running 1 project but i have to logout to open the second project.I can not run both at the same time.
Any help on this pls..
I am creating a switchboard to access a few data entry forms and to preview reports before printing. It seems that whenever I add a button to preview a certain form(the 4th button on the form, all others work fine), Access goes crazy. I get several errors, including:
"You have entered an expression that has an invalid reference to the propery MaxRecButton. "
and
"The Open Form action has been canceled"
Im also getting crashes to desktop and low memory errors.
Now I am getting "referenced memory" "memory could not be wrriten" errors? Is this an Access problem or a "need a computer upgrade" error?
Any clue why this might be happening for this form?
hi,
got a weird question.
Im working on a form atm (duh)
I have a form that exists in more tables.
Now you have 2 solutions: I can use a query and generate the form, or i can make various subforms.
what is the best option? or doesnt it make much diffrence?
I am creating a database. There are two main tables - Table 1 is created via a Make Table query, which pulls from an Oracle db, which is the db for our department's customer service system. Table 2 is populated via a form. Table 1 has personal customer information and Table 2 has order information. The two tables share a Customer ID. Table 2 contains data that was imported from an Excel sheet (what the users had been using up until this db was created). For this example, let's say each customer will only ever have one order so there will be a record to record match. Also, since Table 1 has to be manually refreshed using real-time data, it may lag behind or be ahead of Table 2.
Here's my problem:
I want the user to see the customer's name when he/she types in the Customer ID. I got the DLookup to work for NEW records - that is, for records that exist in Table 1 but have not yet been entered into Table 2 - but it will NOT work for the 300+ records that I imported from Excel. For those records, the Name textbox remains blank or if I type in the Customer ID, gives me an error about entering duplicate values.
Furthermore, if Table 1 is lagging behind Table 2, I want the user to just go ahead and enter all the order information and when he/she goes back to update the order at a later date (after Table 1 has been refreshed), the name to just pop up.
Can anyone help or offer any thoughts?
Thanks.
Ok, a friend of my was on my laptop and he crashed the hardrive. I was able to get the database I have been working on off the computer but when I use my computer at work to try to open it, it opens but says
"Function is not available in expressions in tabel-level validation expresions"
on all the date-time functions. I can't save anything and my queries are not running because of this date/time thing. Anybody know what is going on?