Force data type from Text to Memo in a simple Make Table Query
I'm concatenating fields of various data types that upon completion sometimes
reaches around 500 characters. Not huge, but larger than the Text limitation
to which is what Access 2007 of course converts this. How can I force the
data type to be Memo while I'm in the query so the resulting table displays
all the data without any truncation.
I don't believe you can.
I think you'll have to create the table first, and then append to it.
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
"E...Retrieving Count of rows returned from MS Access Query
probably a pretty routine question here ...
I am trying to determine the number of rows that would be returned prior to executing an Access query .
If the user selects more than 65,536 possible rows, I want to warn them and then either proceed with a subset, or give them the option of cancelling their query and specifying more selective criteria.
It is a dynamic query, which lets the users click on multiple list boxes to select the "Where" component of the excel query, and I want to enable them to select right up to the maximum number of rows.
Ultimately,...Need help with the query.
How can I get desire results below. I cannot seem to figure this out.
Any help is greatly appreciate.
IF OBJECT_ID('Tempdb.dbo.#Temp', 'u') IS NOT NULL
DROP TABLE #Temp
CREATE TABLE #Temp
UserHistoryId INT NULL,
UserId VARCHAR(6) NULL,
EventDate DATETIME NULL
INSERT INTO dbo.#Temp([UserHistoryID],[UserId],[EventDate])VALUES (664,
'User1', CONVERT(DATETIME, 0x00008eac00000000))
INSERT INTO dbo.#Temp([UserHistoryID],[UserId],[EventDate])VALUES (1014,
'User1', CONVERT(DATETIME, 0x00009c2600ef152a)...Update Queries after Table Field Name Changes
In Access 2007 I have a set of tables in which the name of the index field
has been changed. How can I update the queries, based on these tables?
In data view I get the 'Enter Parameter Value' box. Enering the new field
name in various formats gets me no results. Trying to get into design view
the error box
'Databaser can't represent the join expression Table1.oldname=Table2.oldname
in Design View' appears. Underneath it says 'one or more fields may have
been renamed'. Yes, I know. But, how can I update the query to the new
Any help would b...Microsoft Query #8
I'm using Excel to query an oracle database. When I go to Microsoft Query to
edit my query, I can't view my criteria or tables. I click on the view menu
and am unable to select view "criteria" or "tables". Thus, all that shows up
in the screen is the data in my query. This has happend to me before. Can
anybody help me?
I was able to answer my own question. It seems when query is complicated
enough, you are unable to view the criteria, since you are now using SQL to
query the data. It seems that if you click on the SQL button and scroll
down, delete th...Totals Query Help!
Hi, I have a table with the following fields:
What I'm trying to do is write a query that will give me an average Value
for each month, but I want the average to be based only on records where the
Model is consistent across all months. So if Model x is missing from one or
more months all records for that model would be excluded from all the
overall Monthly averages. So the query will only average records where the
Model is present in each month.
Any help with this would be greatly appreciated.
It'll help us if you can ...Formula to display Count of Days in given month, using list of Start and End Dates
I have a large number of rows that contain a start date and end date.
I am attempting to generate a count of days within that date range
that are in a particular month, for example, Jan of 2011.
Any suggestions for a formula that could be used to display the
amounts shown in Column C would be helpful.
A B C
Start Date End Date Count of Days in Jan2011
1/10/11 4/7/11 21
2/6/11 3/1/11 0
12/20/10 2/15/11 31
I think the best way is to create a table f...how do i open spreadsheet from e-mail
i have win xp home edition. Have a time sheet e-mailed to me. To open it I am
told win needs to Know Who created it. Then it tells me microsoft exel but i
have no idea what to do next!
Do you have Excel installed on your computer? It is a component of MS Office
suite and does not come as part of Windows OS.
Do a file search for excel.exe and see if you can find it.
If not, download and install the Excel Viewer from....
You will be able to view and print Excel worksheets.
Gord Dibben Excel MVP
On Tue, 14 Sep 2004 19:11:01 -0700...How to count the maximum number of consecutives?
I need to determine the maximum number of consecutive negative numbers in a
column. The column contains formulas that evaluate to a positive number, a
negative number, zero or "" (blank). I cannot add an additional column. The
following are the results of formulas in cells A1:A11 that need a formula in
A12 that evaluates to 3, the maximum number of consecutive negative numbers
counting cells A2, A6 and A7.
You're sure you can't us an extra column somewhere?? Even a hidden on...Count Duplicate Alerts
I have a spreadsheet that in a simplified form looks like
I need to determine how many different pumps each drug was on. So I
need to return
The spreadsheets I am working with have about 30,000-50,000 rows, so
that may rule out array formulas. I would prefer to not use pivot
tables because the entire table is going into a pivot table for
analysis. If needed, I can resort the table but would prefer not to.
The best layout for my needs would be t...Counting data
Thank you. Now I want to take the sheet that summarizes
the individual dates divided into the 3 times groups - and
total them by month. How can I do that without having to
manually choose each range for each month?
[ Original post in: http://tinyurl.com/69bp2 ]
Do a similar set-up in a new Sheet3
Put in A1: =Sheet2!A1
Copy over the 3 time bands listed in:
B1:B2 : 700, 1500
C1:C2 : 1500, 2300
D1:D2 : 2300, 700
(these will act only as col labels here)
Put in A3 : 1-Jan-2003
Format as Custom, type: mmm-yy (It'll appear as "Jan-03")
Copy A3 d...make table query with related tables
I have an equipment maintenance database, I would like to put my inactive
(gotten rid of) equipment in an inactive table, and not loose the
relationship with the maintenance table. basically make two tables. I did a
make table query, made my inactive equipment table. I could not delete the
equipment from my first table due to key rules. I then made a relationship
from my equipment table to my maintenance performed table. could not make a
one to many and enforce integrity due to the first equipment table. I am
going to delete the relationships, run a delete query on my inactive marked
e...Highlight keywords in result of perameter query
I have a query perameter "*" & [Keyword Search] & "*" the returns the records
based on the keyword. Works great. Is there a way the keyword to be
highlighted or change color in the results?
Not that I know of. Especially in a query.
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
"Ryan Tisserand" <RyanTisserand@discussions.microsoft.com> wrote in message
>I have a query perameter &qu...How do I stop a spreadsheet from Auto-publishing as a web page?
A colleague has taken a copy of a Excel file which was set to automatically
publish as a web page each time the xls was saved.
The copy is in a completely different location and is going to be put to a
different use, however it is still trying to auto-publish when saved.
Consequently, it is coming up with an error.
The error dialog has 'dont show me this again' type checkbox, but I'd rather
remove the problem rather than hide it.
How do I stop the auto-publishing?
...Count + condition
My other problem is that I want to get the number of different
variables (text type) + a condition (date)
I should get the numbers of different variables for each date
2004-08-01 -> 2
2004-08-02 -> 1
2004-08-04 -> 4
Message posted from http://www.ExcelForum.com/
if you create the list of unique dates on a separate sheet
(e.g. using 'Data - Filter - Advanced Filter') in co...Query Based Distribution Lists #3
I can create QBDL based on STATE, ZIP, LASTNAME and many other fields. I
would like to make a list based on Group Membership. There is a field under
USER called MEMBER OF but it does not give me any results when I do a
I have many employees who work in multiple locations and need to belong to
several different groups even though they have only one login and work out of
a single office with the same address etc. So I made them members of groups
based on the different location in which they work and wanted to send mail
based on this membership.
McMurray <McMurray@disc...Interactive Spreadsheet
Can someone provide me a web site of an Interactive Excel spreadsheet which
asks a user some general questions and then uses those answers to develop
worksheets behind the scenes???
Thanks in advance!
Very general question.
Please provide a few more details.
Gord Dibben Excel MVP
On Thu, 16 Sep 2004 13:57:04 -0700, RTP <RTP@discussions.microsoft.com> wrote:
>Can someone provide me a web site of an Interactive Excel spreadsheet which
>asks a user some general questions and then uses those answers to develop
>worksheets behind the scenes???
>Thanks in advanc...using the count function
I have 5 departments and I am trying to count the number of yes votes for
each dept, is this possible
Sure. It's called Countif. See this article:
"barklek" <email@example.com> wrote in message
>I have 5 departments and I am trying to count the number of yes votes for
> each dept, is this possible
> I have 5 departments and I am tryi...Using single button to run multiple query
I want to use single button on the form to run multiple queries. How it can
be done? Where should I write queries and code?
One way to do this would be to create your queries, then use the command
button to run code that calls each query. Another approach would be to
create all your queries, then create a macro that runs all your queries, and
call that macro using code behind a command button.
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor
"Gajanan&qu...Missing records using a timestamped field to query.
I'm trying to extract a range of information using a timestamp field through
a select query. I'm using Between[Date] and [Date]. It's not caputuring all
of the records, specifically the records w/ the 1st and the very last days of
the range. I tried doing something like this "Date([Timestamp])" but to no
avail. Any thoughts as to why?! Thanks, Michael
On Tue, 12 Feb 2008 08:39:02 -0800, Michaelcip
>I'm trying to extract a range of information using a timestamp field through
>a select query. I'm...Event Log Queries
Ok, so I'm working on querying multiple DC's to look for a specific
event ID (645 in the security logs, for computers recently joined to
the domain). I thought I had everything figured out, but it's still
not working right. Running the script, it just somewhat hangs...not
sure if that's just because it's still searching or not and is slow.
Let me know if you guys have any thoughts...thanks!
Set objDictionary = CreateObject("Scripting.Dictionary")
arrComputers = Array("DC02", "DC03")
iReach = 0
For Each st...displaying Query results to subform
First--the disclaimer--I am in no way a programmer, but I am working
diligently to support my team in any way possible. With that said, I have
painstakingly created a simple build SQL string code in Access Visual Basic
Private Sub Command8_Click()
Dim SQL As String
SQL = "SELECT * " & _
"FROM [BCSG_CARDFILE] " & _
"WHERE ((([BCSG_CARDFILE].[IMPORT_FILENAME]) Between " & _
"""" & _
Forms![BCSG_CARDFILE_SEARCH]!cboDateFrom.Value & _
"""" & _
" And &quo...Query to Filter by Dates, Using Form
I’m kind of stuck on a query! This works fine, when I just filter for
Customers and Traders. Then, I went ahead and added in a couple of
ComboBoxes, to allow me to choose a From-Date and a To-Date…now my dynamic
query doesn’t work anymore…
This SQL Works fine for Cust and Trader, but not for dates:
WHERE Trades.[Cust] IN('') AND Trades.[Trader] IN('');
Below is my SQL for filtering by dates, but it DOES NOT WORK:
WHERE ((Trades.Tdate) Between [forms]![SearchForm]![cboFrom] And
[forms]![SearchForm]![cboTo]) AND Trad...Spreadsheet/VBA Consulting
Assignment will be due soon but you do not have an easy way to finish it?
Tired of spending hours on time-consuming, labor intensive work?
Try UDQ Consulting Services -
Consulting Cost Starting from $25.00 - Your Small Investment Could Save Your
Hours Even Days of Labor-Intensive Repetitive Work!
Most of the time, you can receive solution to your problem within 24-72
UDQ Consulting Services Believes that Every Problem Should Have an Easy &
Your "Impossible" Task Could Be Someone Else's &quo...Query wildcard symbols
Just a heads-up in case this bites you, too.
I migrated an application from Access 2002 to Access 2007.
I have a search form for the users to enter some criteria ... the form
dynamically builds a SQL WHERE clause before opening a "results" form based
on an underlying query that includes all the fields available to search on.
Once in Access 2007 format, the search form opens empty every time?!
When I add selection criteria to the underlying query directly, the result
set is empty every time?!
Oh wait, I'm using the "*" (anything/everything) wildcard character. W...