Find field query
I have inherited a Access 2000 database which up until now has allowed me to
search for a record using the "Find" action searching by a field.
Now if I add a new record and save it, I then go back to search for it using
the above method and it says that Access can not find the record even though
I know the record is there when I check on the original table.
What has happened and how do I resolve it?
Urgent assistance please!!!!!!!!!!!!!
...load query results into ado recordset
I want to loop through query results in vba. This query is in the
same access db as the vba module. I thought I wanted to load the
query results into an ado recordset, but cannot find the syntax. I
can re-create the query in vba, substituting the wildcard character
"%" for "*", and that works, but is not what I want.
I want to be able to manipulate the query in access, then process the
results in vba, one row at a time.
...Why does my update query appear to do nothing?
I am running the following code:
Dim strsql As String
strsql = "UPDATE TM_Boat SET TM_Boat.FK_Customer = " & [Forms]!
[FM_ChangeBoatOwner]![Combo36] & _
" WHERE (((TM_Boat.PK_Boat)=" & [Forms]![FM_ChangeBoatOwner]!
[TXT_BoatNum] & "));"
CurrentDb.Execute strsql, dbFailOnError
MsgBox CurrentDb.RecordsAffected & " Records updated"
It keeps telling me "0 Records Updated", although it IS updating the
Any idea why?
Probably because the database instance created wi...RecordSource Query Help
I'm new to Access, and am learning it on the job. I'm working wit
I recently took the forms from one database and paired them with th
tables from another. It was mostly seamless, but when I open
particular form, I get an error dialog box stating:
"The Record Source '<SQL STATEMENT>' specified on this form or repor
does not exist."
The thing is, I can't find this SQL Statement anywhere. I've trie
viewing the RecordSource field (in Properties) for every Form in m
database, and it's not present in any of them. I also searc...Creating a Group By query from more than one table
Is it possible when creating a report that includes the results of this
query, to also create a pie chart or bar graph using the query information?
And is it possible to tally results from more than one table list on the
same Group By query?
On Tue, 26 Jun 2007 16:46:00 -0700, L. Edwards
>Is it possible when creating a report that includes the results of this
>query, to also create a pie chart or bar graph using the query information?
Sure. They'd be independent objects though.
>And is it possible to tally...Query Parameter
Is there a way to use a query parameter with an expression. Or reference a
textbox value within an expression. In the expression below I would like to
replace the 9/1/2010 date with a reference to a textbox or parameter message
Thanks for any help
This should work in most queries.
IIf(DateDiff("d",qryActive_Agent![Hire Date],CDate([Wha...Form parameter Query
I'm hoping someone can help me with this . I'm trying to get my query to use
a form to get the query parameters. When I run the query, it displays the
text "[Forms]![frmEE]![cboEE]" as a prompt rather than displaying the form
where the user can make a selection.
I'm using the build wizard to construct the following query:
Any and all contributions are greatly appreciated ...
Thanks Karl. You've been a big help. Have a good one.
"KARL DEWEY"...Printing Query
Is there a way of printing an area which printed landscape would spread
over 2 pages as 1 page (not by fitting to page) but by printing what
would appear on page 2 below that on page 1 as the data is not deep
(area to be printed is P5..AK16)
...SQL query for Smartlist
Is there any way to create a field in a SQL query that will appear in
Smartlist Builder as available for choice in the Negative Values tab?
In SQL, you do not "create a field", you define columns, but more
specifically in your case, you will be reading a column and creating a
transformation for the row values in that column to reflect a negative value.
You can accomplish this as follows:
CREATE VIEW fooView AS
SELECT col1, -col2, -col3,.., FROM fooTable WHERE fooExpression;
Note that by inverting the sign of the numeric columns col2, and col3, I am
abl...What type of query?
I have records in my Access Database Version 9.0 with these basic fields,
LName, FName, PtNumber, AdmitDate, DCDate. The Names and PtNumber are
already entered and there are 2000 records. Each week I get a report that
has all of our dicharged patients with many fields including PtNumber,
AdmitDate and DCDate populated (about 100 records a week). So far we are
manually entering these records. I think I read somewhere that info from
excel can be imported to access so I am wondering how I can do this. I used
a dataminer program (Monarch) and exported these fields AcctNum...Vlookup query that checks 2 conditions
I've posted this before without too much luck so here it goes again...
I'm trying to automate the creation of a vendor report that lists al
vendor sales. Basically I need a formula that, based on a unique vendo
- check colomn A for the vendor number match
- then check column B to see if there is a buyer number (which mean
that the item is sold)
- then dumps then 'nth' occurance of the value / text from the column
I'm currrently using the formula:
0,INDEX(VendorReportData,SMALL(IF(VendorReportData=$N$13,ROW(Catalog!$A$2:$...Access 2002 queries
Why , when accessing a table in the database , you can have greater than 127
fields for an insert and retreive but are limited to 127 fields for an update.
Guessing that you have something like
Set TableA.FieldA = TableB.FieldA,
TableA.FieldB = TableB.Fieldb
GUESSING (again) that is probably being counted as four fields in the query.
So if you try to update more than 127 fields (2*127=254; 2*128 = 256) you
are running into the maximum of 255 fields allowed in a query.
If you really need to update more than 127 fields at one time then I suggest
you will have to break the upda...Making attribute a checkbox
How do you make an attribute a checkbox
In Customization, open the entity.
Than open click on Forms and Views and open the Form.
Double click on bit type of field.
In new window, the second tab will be formatting.
You will have option in bottom, "Control Formatting"
here you can choose the desrired format.
PLEASE do click on Yes or No button if this post was helpful or not for our
Email for direct contact: imumar at gmail dot com
> How do you make an attribute a checkbox
Create a bit field an...Query combining the tables
cons dcity dst dzip ocity ost ozip
e f jk 7789 fg cd 989
c o lk 970 sf9 cdf 9890
cdd yf mh 979 hgg mkhi 7699
cons dcity dst dzip ocity ost ozip
a d ak 560 b c 789
b e ck 869 de ef 970
c o lk 970 bh mk 976
the output qery should combine data from both tables, should include
all rows from both tables and should have the following fields.------
the data should be combined from both tables across consignee, dcity,
dstate and dzip fields
t...nz function problem-query to form
I am using the nz function in my queries. The function works at that level.
However, when I try to open the form it won't allow me to. If I take out the
nz function everything works fine.
Could be an issue with the data type. Try an IIf() expression instead.
More details and example:
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"southbender" <southbender@disc...Dialogsheet Checkbox to Select All Checkboxes
I am using a dialogsheet to get user input. The dialogsheet is created at
runtime. The amount of checkboxes on the dialogsheet varies based on how many
tabs are hidden. I would like to have a checkbox on the dialogsheet that
allows users to "select all" checkboxes.
Dim SheetCount As Integer
Dim CurrentSheet As Worksheet
Dim PrintDlg As DialogSheet
Dim cb As CheckBox
Set CurrentSheet = ActiveSheet
Set PrintDlg = ActiveWorkbook.DialogSheets.Add
'some code used to loop and build checkboxes not shown here
If SheetCount <> 0 Then
If ...xpath query with more specifications
I've written a program which uses this XPath query:
as you better know, it finds and locates "SomeString" under any path within
an xml file.
The thing is that I don't know how to make it ignore those nodes which
have children and focus on nodes that have a text only. Like:
You can use the text() function to evaluate only the text...Select query help
I have tblLog. It's fields are:
I need the SQL to return all records where 'Credited' is false, 'QSL_R' <> NULL or
blank, and whose combination of 'Freq' and 'Mode' for a given 'CID' have 'Credited' =
false, but not if that record has a 'mate' whose 'CID', 'Freq/Mode', 'QSL_R' all
match, and 'Credited' is True.
I'm not sure if my description is understandable, so here is an example.
Call Freq Mode...Business Portal deployment and queries #2
I would like to know how to transfer our customized queries in a BP
When we deployed BP from our test environnement to production it worked
smooltly for the sharepoint part but the problem is that the queries didn't
follow... We don't want to recreate all customized queries on each
I pretty sure that the queries are in the Dynamics database. I tried to
backup the test version of that DB and restore it in prod but after that BP
refused to start-up...
So, is the solution to export only certain Dynamics tables data ? If yes,
which tables ? M...database query select the "top record" only
good afternoon All,
I would like to know the SQL to select only the "top" record of a query
sorted by date/number or operation of numbers e.g. max of date, min of
stock, etc so I only retrieve the first and most important value instead of
a set of several values.
here below an EG from a query I use every day:
SELECT pordtl.pdtiid, pordtl.pdtvds, pordtl.pdtluc, pordtl.pdtvum,
pordtl.pdtwhs, pordtl.pdtode, pordtl.pdtvdr
FROM beth.pordtl pordtl
GROUP BY pordtl.pdtiid, pordtl.pdtvds, pordtl.pdtluc, pordtl.pdtvum,
pordtl.pdtwhs, pordtl.pdtode, pordtl.pdtvdr
HAVING (pordtl.pdtiid I...Access Report on Parameter Query
I have a report established identifying data based on a query for a single
report month. Amounts are identifed by program and totaled. One of the
categories are then segregated and the balance totaled. The report is built
on the query but the formula used to segregate that category pulls from the
table as follows:
=DLookup(“[SAL & BEN]”,”CLIENT SUPPORTS”,”[MONTH REPORT]=#”&[MONTHREPORT]
&”# AND [ADMIN]=’PA’ “)
I need to segregate the same category for the parameter query which pulls
multiple report months but have been unsuccessful thus far. Suggestions?
If ...checkbox with relative reference?
I have a checkbox in E5 controlling E5. If I copy it to K11, I want it
to control K11.
Now, how do I give it this kind of relative reference?
I take it you're going to have to do this more than a few times. I suggest
you create the checkbox objects in code rather than using the Excel Control
Toolbox. Add the checkboxes in the workbook open event and then you can set
the attributes of each using variables.
"Sven Berg" <firstname.lastname@example.org> wrote in message
>I have a check...Removing choices from combobox query but stilling showing for older entries
I have a form, frmMain that lists staff names in a combobox from a query
into a lookup table. The table, lkpStaff has 3 fields, StaffID, Staff and
ShowInViews. The RowSource Query for the combobox is:
SELECT lkpStaff.ThingID, lkpStaff.Thing FROM lkpStaff WHERE
The combobox ControlSource is the field Staff in tblMain which is populated
By unchecking ShowInViews for staff that are no longer employed I can remove
them from the combo's dropdown. However, of course, it also removes those
staff names from older entries. I w...Checkbox checked then show more checkboxes
I have added checkboxes from Control Toolbox and they seem to work fine. I
just want to make one of them lets say checkbox3 to show more checkboxes when
checkbox3 is marked. How do I do that?
I'd add them all to exactly where I wanted them.
But then have the "master" checkbox just unhide/hide the others.
Private Sub CheckBox3_Click()
Dim ShouldBeVisible As Boolean
ShouldBeVisible = CBool(Me.CheckBox3.Value = True)
Me.CheckBox1.Visible = ShouldBeVisible
Me.CheckBox2.Visible = ShouldBeVisible
Me.CheckBox4.Visible = ShouldBeVisible
...Copying Checkbox Values into a different worksheet
I'm trying to copy the value of several checkboxes to another worksheet.
This works basically all fine by using the code below
d = Worksheets("Sheet1").CheckBox1.Value
Selection = d
But, as there are about 40 checkboxes I wonder whether there is a way to do
this all automatically, i.e. by some for loop going through all the
As I am a complete rookie in Excel I appreciate any hint / suggestions.