How to select day_of_week and/or date given a period?
I have a table with the following columns:
Both, Date and DayOfWeek can be NULL but only one of them can be non-NULL.
DayOfWeek, if not NULL, can have a value 0 - 6 (0 = Sunday).
When given a period (i.e. date range) I need to select rows that satisfy the
1) both, Date and DayOfWeek are NULL
2) if Date is not NULL then it must fall within the period
3) if DayOfWeek is not NULL then it must be included in the period
Could someone please help me with the query?
T...Why rows get larger when move a column?
Operating System: Mac OS X 10.4 (Tiger)
Processor: Power PC
Sometimes I move a column and then ALL THE rows in a workbook become very large and equal in size. Happens all time. why? thanks.
...Macro pause for input
I would like a macro to insert a footer with some data
already in a worksheet. I can do that, but I want it to
pause and offer choices, e.g. a dropdown box or something,
so that the choice is between two or more cells/named
ranges in the worksheet, or choices such as left or right
footer and Active.Workbook.FullName or "Date" such as date
is "Date: &D"
Suggestions greatly appreciated.
Have you tried using Forms in your macro? Or even using
the msgbox command will probably suffice.
Look msgbox up in the help from your macro
>...Visible control based on other inputs
I have a form where the user choose shift, Sektor, Cell, Date from 4
The cell combobox is based on a query that gives you the cells that
corisponds to a sektor when the sektor is choosed.
The query looks like this
SELECT TblCelle.CelleNr, TblCelle.CelleTxt, TblCelle.Sektor
ORDER BY TblCelle.CelleNr;
TblCelle has the following fields
CellNo, CellTxt, CellVar, CellCat, Sektor
The CellVar can have two input either L or H
I have a text box and a label that should not be visib...How do I format cells that get their data from an MySQL dB?
I have a spreadsheet that uses an MS Query to connect to a MySQL
database to retrieve data.
One of the columns that gets populated is a phone number column.
The phone numbers in the database are unformatted (i.e. 6052223456)
I'd like to format the column so that the data is presented using a
phone number mask (i.e. (605) 222-3456 )
However, I've tried the Format Cells and the setting Special -> Phone
Number doesn't do anything to format the cells.
I just copied one of the rows from the query spread sheet to a new
normal spread shee...Input music to WMP?
I know you can enter music by indicating what folder to watch, but I want
more control. Amazon Downloader is a free program to do this, but it is done
programmatically. Is there a non-programmatic way to do this?
On Mon, 29 Mar 2010 17:50:28 -0400, "Jim Cassilly"
>I know you can enter music by indicating what folder to watch, but I want
>more control. Amazon Downloader is a free program to do this, but it is done
>programmatically. Is there a non-programmatic way to do this?
Hi, it's not...Best way to get email between two AD forests
Hopefully someone can give me a good idea =)
My current setup is two AD forests. Both have exchange 2003 servers,
but they are not live. Users in both forests have been using email
through a POP host on the internet. Both forests have the same email
address and have been using email through an external POP host.
The MX record will be changed to point to the static T1 line of Forest
What is the best way to get Forest B users to read emails?
1) Setup a VPN between the two forests. What I don't like though is
that a user has to be setup in BOTH forests to be able to connec...Get mouse status
Is it possible to get the current status of mouse left button at
Look at the GetAsyncKeyState API.
"William" <firstname.lastname@example.org> wrote in message
> Env: VC++6.00
> Is it possible to get the current status of mouse left button at
I have a spreadsheet set up containing products with a uniq reference code. I need to input sales quantities against these products each week. At present this is done manually. Has anyone any ideas how to auto mate this function. The sales quantities can be downloaded into an excel file
you may have a look at pivot tables if I understood you
>I have a spreadsheet set up containing products with a
uniq reference code. I need to input sales quantities
against these products each week. At present this is done
manually. Has an...Is it Possible to get rid of a System created Realtionship
The situation is that I would like it so that opportunities will only be able
to be associated with Accounts. Currently they can be associated with
Accounts or Contacts. Anyone have any ideas on how I could accomplish this
since I cannot delete the system created relationship between Contacts and
What you can do is hide this relationship from the user (remove the original
lookups and set the relationship on the other end to no be displayed in the
menu). Then create a new relationship between account and opportunity.
If need be you can copy the value from the new looku...Getting Attribute value by using name
I am getting the element node list, and if any attributes are there, I am
getting the attributes values by using the index
0- first element
1- second element
But for some reason the order of the attributes is swapped , so my question
is , how to get the attribute value using a name instead of the index values.
I am new to xml and thanks for any help.
Console.WriteLine("Count=" + addressNodeList.Count);
foreach (XmlNode aNode in addressNodeLi...User input
I've used this forum before, and found it very useful.
I need to set up a worksheet, (worksheet1), to allow a
user to input surname,initials,id,location,date for
office files that move around the office. The result will
be stored in worksheet2 in 1 row and 5 columns called
Surname - Inits - ID - Location - Date
Each entry will be recorded in the next empty row in
worksheet2. I think I have found a way to identify the
next empty row using the Indirect function.
But, how can I set up worksheet1 to allow the user to
input the data he has which is then automatically
recorded into wo...Help with simple code getting frostrated
My table named "paid" has the following fields,
I want a main form should lookup the last pledge, from the customer, and
deduct any PaidAmount afterwards, I have the following code
Private Sub Form_Current()
Lastdate = dlast("date1","paid","pledgeAmount"<>0)
'enter data calculation as new balance
!NewBalance = Dsum (0 + "PledgeAmount"-"PaidAmount","Paid","CustomerId="& Me!
CustomerId &" And Date1 >= #"& Lastdate & &...send email from VB with minimum user input, mapi, cdo?
I want to add the ability for the user to email an attachment after
generating a report, but I don't want to have to rely on them for much
help in setting up the email, a lot of them would have trouble finding
their outgoing mail server, whether it requres authentication etc.
Is it possible to send mail from VB after looking up the outgoing server
and any authentication that may be required from the registry, just
asking the user to enter the destination email address?
And is there a recommended method for sending email that will work on XP
On Aug 13, 6:1...Payroll:verfying the pay period for same employee
at the time of building the cheque the Pay Period should be
if it is done for the same pay period before
so the pay period should have id so it could be verified
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
...form: data input locking up
I have a form which was working fine until a few hours ago. The form has
several sub forms. However, those are working fine. I am unable to
edit/add/delete records on the main form. This is a front end database, with
the main data storage on a network. I moved the data starage DB onto my
local drive, reset the links and experienced the same problem. The form is
not based on a query and writes directly into a data table. The form is
normally called up (filtered) through code. however, I experience the same
problem when I open it up directly (without filtering the re...Keep getting error from code
Good morning. Good to see the board back up.
Here's my problem. I have a form with a staff Id. These Ids are unique
because there are no duplicates. I click on "New" for new record. Then have
the code check to see if it is a duplicate record
Private Sub StaffId_BeforeUpdate(Cancel As Integer)
10 On Error GoTo Err_Form_BeforeUpdate
20 If Not IsNull(DLookup("[staffid]", "tblMain", "[staffid] = '" & Me!
[StaffId] & "'")) Then
30 Select Case MsgBox("Sorry, This is a duplicate Staff Id Number,"...Input cell should display a percentage of input value in the cell
Please read the below discussion and suggest an answer
Many thanks for ur answer
what if i want to change B1:B700(but want to leave one cell for sum total in
every 8 cells down), similarly in C1:C700, & D1: D700
Life isa journey not a destination
"Gary''s Student" wrote:
> Put this event macro in the worksheet code area:
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim r As Range
> Set r = Range("B9")
> If Intersect(r, Target) Is Nothing Then Exit Sub
> Application.EnableEvents = False
> r.Value = ...Re: Locking textbox input
Is there a way to enter info in a textbox ONCE and as soon as Tab or
Enter is pressed it locks automatically... so it wont be altered....
Message posted from http://www.ExcelForum.com/
how about not only a textbox but a plain old cell??
Message posted from http://www.ExcelForum.com/
How about anytime you get out of it?
I added a textbox from the control toolbox toolbar to a worksheet.
I double clicked on it and replaced this code:
Private Sub TextBox1_Change()
Private Sub TextBox1_LostFocus()
Me.TextBox1.Enabled = CBool(Me.T...subforms: using input from one subform to update the other subform
Hello. I'm hoping that someone can help me.
I have 2 subforms on separate tabs. Based on a user's input on subform 1, I
would like a combo box on subform 2 to be enabled or disabled.
Does anyone have a solution? Please let me know. Thanks in advance.
Controls on subforms are always referred to via the parent form. See
http://www.mvps.org/access/forms/frm0031.htm at "The Access Web" for
Note that depending on how the forms were added as subforms, the name of the
subform control may be different than the name of the form being used as a
sub...Get fields in worksheet
I have records which have four columns. I have a listBox
in User Form which shows all records. I would like to get
all four fields of the record in worksheet cells when I
will select a record from the ListBox. Is it possible?
How about this:
Private Sub ListBox1_Click()
Dim iCtr As Long
Dim DestCell As Range
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
If .ListIndex > -1 Then
For iCtr = 0 To 3
DestCell.Offset(0, ...Counting instances in a period
Norwegian law says that the employer have to pay for the first 16 day
period when a person is on sick leave, after that period the state
compensates the employee.
So if a emplyee was taken ill in this pattern, the employer will pay
her for all instances except the last, since it is outside the 16 day
period, but the illness started within it.
Do anyone have a good formula that I could use to calculate which days
I have to pay for and which I don't?
01.01.07 ill have to pay
09.01.07 ill have to pay
1...Keep getting "Type mismatch"
'I do not understand this error. I first tried without the if statement and
10: that was not the problem. I tried changing the dim statement from 25 to
23 that did not work. I think I am initilizing it right. Just not sure.
Dim deg(25), min(25), sec(25), DEGt(25), Zodiac(25) As Double
For h = 1 To 23
If .Cells(2 + h, 3) = "" Then
DEGt(h) = ""
deg(h) = CDbl(.Cells(2 + h, 3))
min(h) = CDbl(.Cells(2 + h, 4))
sec(h) = CDbl(.Cells(2 + h, 5))
DEGt(h) = deg(h) + min(h) / ...SetGet control input
What is the best way to get and set the contents of an EditControl
(in a FormView) , other than using DDX.
Could I use the below, and how would I get the handle of the FormView?
HWND hDlg, // handle of dialog box
int nIDDlgItem, // identifier of control
LPTSTR lpString, // address of buffer for text
int nMaxCount // maximum size of string
"RB" <NoMail@NoSpam> wrote in message
> What is the best way to get and set the contents of an EditCon...Select worksheet with Application Input box???
Could someone let me know how to Select the Name of the
Worksheet with Application Input box.
Right now in my Macro I am providing the user with an
option to select the Excel file. The code is as follows:
ExcelFile1 = Application.GetOpenFilename("Excel-
1, "Select Input File To Open", , False)
If TypeName(ExcelFile1) = "Boolean" Then Exit Sub
Debug.Print "Selected file: " & ExcelFile1
After selecting the Excel File, the user must be Provided...